Ekinlikler
31 Mar 23 - 2 Nis 23
Sql, Power BI, Fabric ve yapay zeka topluluğu tarafından yönetilen nihai etkinlik. 31 Mart - 2 Nisan. 150 ABD doları indirim için MSCUST kodu kullanın. Fiyatlar 11 Şubat'ta artış gösterir.
Bugün kaydolunBu tarayıcı artık desteklenmiyor.
En son özelliklerden, güvenlik güncelleştirmelerinden ve teknik destekten faydalanmak için Microsoft Edge’e yükseltin.
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric
Learn about which columnstore features available for each version of SQL Server, and the latest releases of SQL Database, Azure Synapse Analytics, and Analytics Platform System (PDW).
This table summarizes key features for columnstore indexes and the products in which they are available.
Columnstore Index Feature | SQL Server 2012 (11.x) | SQL Server 2014 (12.x) | SQL Server 2016 (13.x)1 | SQL Server 2017 (14.x) | SQL Server 2019 (15.x) | SQL Server 2022 (16.x) | SQL Database1 | Azure Synapse Analytics dedicated SQL pool |
---|---|---|---|---|---|---|---|---|
Batch mode execution for multi-threaded queries2 | yes | yes | yes | yes | yes | yes | yes | yes |
Batch mode execution for single-threaded queries | yes | yes | yes | yes | yes | yes | ||
Archival compression option | yes | yes | yes | yes | yes | yes | yes | |
Snapshot isolation and read-committed snapshot isolation | yes | yes | yes | yes | yes | yes | ||
Specify columnstore index when creating a table | yes | yes | yes | yes | yes | yes | ||
Always On supports columnstore indexes | yes | yes | yes | yes | yes | yes | yes | yes |
Always On readable secondary supports read-only nonclustered columnstore index | yes | yes | yes | yes | yes | yes | yes | yes |
Always On readable secondary supports updateable columnstore indexes | yes | yes | yes | yes | ||||
Read-only nonclustered columnstore index on heap or B-tree | yes | yes | yes 3 | yes 3 | yes 3 | yes 3 | yes 3 | yes 3 |
Updateable nonclustered columnstore index on heap or B-tree | yes | yes | yes | yes | yes | yes | ||
Additional B-tree indexes allowed on a heap or B-tree that has a nonclustered columnstore index | yes | yes | yes | yes | yes | yes | yes | yes |
Updateable clustered columnstore index | yes | yes | yes | yes | yes | yes | yes | |
B-tree index on a clustered columnstore index | yes | yes | yes | yes | yes | yes | ||
Columnstore index on a memory-optimized table | yes | yes | yes | yes | yes | yes | ||
Nonclustered columnstore index definition supports using a filtered condition | yes | yes | yes | yes | yes | yes | ||
Compression delay option for columnstore indexes in CREATE TABLE and ALTER TABLE |
yes | yes | yes | yes | yes | yes | ||
Support for nvarchar(max) type | yes | yes | yes | yes | no 4 | |||
Columnstore index can have a non-persisted computed column | yes | yes | yes | |||||
Tuple mover background merge support | yes | yes | yes | yes | ||||
Ordered clustered columnstore indexes | yes | yes | yes | |||||
Ordered non-clustered columnstore indexes | yes |
1 For SQL Database, columnstore indexes are available in Azure SQL Database DTU Premium tiers, DTU Standard tiers - S3 and above, and all vCore tiers. For SQL Server 2016 (13.x) SP1 and later versions, columnstore indexes are available in all editions. For SQL Server 2016 (13.x) (before SP1) and earlier versions, columnstore indexes are only available in Enterprise Edition.
2 The degree of parallelism (DOP) for batch mode operations is limited to 2 for SQL Server Standard Edition and 1 for SQL Server Web and Express Editions. This limitation refers to columnstore indexes created over disk-based tables and memory-optimized tables.
3 To create a read-only nonclustered columnstore index, store the index on a read-only filegroup.
4 Not supported in dedicated SQL pools but is supported in serverless SQL pool.
SQL Server 2022 (16.x) added these features.
Ordered clustered columnstore indexes improve performance for queries based on ordered column predicates. Ordered columnstore indexes can improve performance by skipping segments of data altogether. This can drastically reduce IO needed to complete queries on columnstore data. For more information, see segment elimination. Ordered cluster columnstore indexes are available in SQL Server 2022 (16.x). For more information, see CREATE COLUMNSTORE INDEX and Performance tuning with ordered clustered columnstore indexes.
Predicate pushdown with clustered columnstore rowgroup elimination of strings uses boundary values to optimize string searches. All columnstore indexes benefit from enhanced segment elimination by data type. Starting with SQL Server 2022 (16.x), these segment elimination capabilities extend to string, binary, and guid data types, and the datetimeoffset data type for scale greater than two. Previously, columnstore segment elimination applied only to numeric, date, and time data types, and the datetimeoffset data type with scale less than or equal to two. After upgrading to a version of SQL Server that supports string min/max segment elimination (SQL Server 2022 (16.x) and later versions), the columnstore index will not benefit this feature until it is rebuilt using a REBUILD or DROP/CREATE.
For more information on added features, see What's new in SQL Server 2022.
SQL Server 2019 (15.x) adds these new features:
Starting with SQL Server 2019 (15.x), the tuple mover is helped by a background merge task that automatically compresses smaller OPEN delta rowgroups that have existed for some time as determined by an internal threshold, or merges COMPRESSED rowgroups from where a large number of rows has been deleted. Previously, an index reorganize operation was needed to merge rowgroups with partially deleted data. This improves the columnstore index quality over time.
SQL Server 2017 (14.x) adds these new features.
SQL Server 2016 (13.x) adds key enhancements to improve the performance and flexibility of columnstore indexes. These improvements enhance data warehousing scenarios and enable real-time operational analytics.
A rowstore table can have one updateable nonclustered columnstore index. Previously, the nonclustered columnstore index was read-only.
The nonclustered columnstore index definition supports using a filtered condition. To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.
An in-memory table can have one columnstore index. You can create it when the table is created or add it later with ALTER TABLE (Transact-SQL). Previously, only a disk-based table could have a columnstore index.
A clustered columnstore index can have one or more nonclustered rowstore indexes. Previously, the columnstore index did not support nonclustered indexes. SQL Server automatically maintains the nonclustered indexes for DML operations.
Support for primary keys and foreign keys by using a B-tree index to enforce these constraints on a clustered columnstore index.
Columnstore indexes have a compression delay option that minimizes the impact of the transactional workload on real-time operational analytics. This option allows for frequently changing rows to stabilize before compressing them into the columnstore. For details, see CREATE COLUMNSTORE INDEX (Transact-SQL) and Get started with Columnstore for real-time operational analytics.
Columnstore indexes support read committed snapshot isolation level (RCSI) and snapshot isolation (SI). This enables transactional consistent analytics queries with no locks.
Columnstore supports index defragmentation by removing deleted rows without the need to explicitly rebuild the index. The ALTER INDEX ... REORGANIZE
statement removes deleted rows, based on an internally defined policy, from the columnstore as an online operation
Columnstore indexes can be access on an Always On readable secondary replica. You can improve performance for operational analytics by offloading analytics queries to an Always On secondary replica.
Aggregate Pushdown computes the aggregate functions MIN
, MAX
, SUM
, COUNT
, and AVG
during table scans when the data type uses no more than 8 bytes, and is not a string data type. Aggregate pushdown is supported with or without GROUP BY
clause for both clustered columnstore indexes and nonclustered columnstore indexes. On SQL Server, this enhancement is reserved for Enterprise edition.
String Predicate pushdown speeds up queries that compare strings of type VARCHAR/CHAR or NVARCHAR/NCHAR. This applies to the common comparison operators and includes operators such as LIKE
that use bitmap filters. This works with all supported collations. On SQL Server, this enhancement is reserved for Enterprise edition.
Enhancements for batch mode operations by leveraging vector based hardware capabilities. The Database Engine detects the level of CPU support for AVX 2 (Advanced Vector Extensions) and SSE 4 (Streaming SIMD Extensions 4) hardware extensions, and uses them if supported. On SQL Server, this enhancement is reserved for Enterprise edition.
New batch mode execution support for queries using any of these operations:
SORT
COUNT/COUNT
, AVG/SUM
, CHECKSUM_AGG
, STDEV/STDEVP
COUNT
, COUNT_BIG
, SUM
, AVG
, MIN
, MAX
, and CLR
CHECKSUM_AGG
, STDEV
, STDEVP
, VAR
, VARP
, and GROUPING
LAG
, LEAD
, FIRST_VALUE
, LAST_VALUE
, PERCENTILE_CONT
, PERCENTILE_DISC
, CUME_DIST
, and PERCENT_RANK
Single-threaded queries running under MAXDOP 1
or with a serial query plan execute in batch mode. Previously, only multi-threaded queries ran with batch execution.
Memory optimized table queries can have parallel plans in SQL InterOp mode both when accessing data in rowstore or in columnstore index.
These system views are new for columnstore:
These in-memory OLTP-based DMVs contain updates for columnstore:
Applies to: SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics dedicated SQL pool
SQL Server 2014 (12.x) introduced the clustered columnstore index as the primary storage format. This allowed regular loads as well as update, delete, and insert operations.
SQL Server 2012 (11.x) introduced the nonclustered columnstore index as another index type on rowstore tables and batch processing for queries on columnstore data.
INSERT
, DELETE
, and UPDATE
operations; to perform these operations you must drop the index, update the table and rebuild the columnstore index. You can load additional data into the table by using partition switching. The advantage of partition switching is you can load data without dropping and rebuilding the columnstore index.Ekinlikler
31 Mar 23 - 2 Nis 23
Sql, Power BI, Fabric ve yapay zeka topluluğu tarafından yönetilen nihai etkinlik. 31 Mart - 2 Nisan. 150 ABD doları indirim için MSCUST kodu kullanın. Fiyatlar 11 Şubat'ta artış gösterir.
Bugün kaydolunEğitim
Modül
SQL Server 2022 T-SQL geliştirmelerine giriş - Training
SQL Server 2022, performansı en iyi duruma getiren ve SQL nesneleri işleme ve oluşturma konusunda daha iyi denetim sağlayan güçlü araçlar ve özellikler sunan bir dizi yeni özellik ve geliştirme sunar.