Scalability and Performance Enhancements (Database Engine)
Scalability and performance enhancements in the Database Engine include columnstore indexes.
Columnstore Indexes
The SQL Server 2012 introduces a new data warehouse query acceleration feature based on a new type of index called an xVelocity memory optimized columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times because columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive. For more information, see Columnstore Indexes.
Online Index Create, Rebuild, and Drop
Indexes containing XML, varchar(max), nvarchar(max), and varbinary(max) columns can now be created, rebuilt, and dropped as an online operation.
Partition Support Increased
SQL Server 2012 supports up to 15,000 partitions by default. In earlier versions, the number of partitions was limited to 1,000 by default.
FILESTREAM Filegroups Can Contain Multiple Files
A FILESTREAM filegroup can contain more than one file. For a code example that demonstrates how to create a FILESTREAM filegroup that contains multiple files, see CREATE DATABASE (Transact-SQL).
You can improve I/O scalability for FILESTREAM data by placing different files within the same FILESTREAM filegroup on different volumes. This feature eliminates the need for complicated workarounds that use partitioning and multiple FILESTREAM filegroups.