資料行存放區索引:概觀Columnstore indexes: Overview

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse

「資料行存放區索引」是儲存和查詢大型資料倉儲事實資料表的標準。Columnstore indexes are the standard for storing and querying large data warehousing fact tables. 此索引使用以資料行為基礎的資料儲存和查詢處理,相較於傳統的資料列導向儲存,最高可在您的資料倉儲中達到 10 倍的查詢效能改善。This index uses column-based data storage and query processing to achieve gains up to 10 times the query performance in your data warehouse over traditional row-oriented storage. 相較於未壓縮的資料大小,您也可以將資料壓縮提升高達 10 倍。You can also achieve gains up to 10 times the data compression over the uncompressed data size. SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始,資料行存放區索引可啟用作業分析:在交易式工作負載上執行高效能即時分析的能力。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, columnstore indexes enable operational analytics: the ability to run performant real-time analytics on a transactional workload.

深入了解相關案例:Learn about a related scenario:

何謂資料行存放區索引?What is a columnstore index?

資料行存放區索引是使用單欄式資料格式 (稱為 資料行存放區) 儲存、擷取及管理資料的一項技術。A columnstore index is a technology for storing, retrieving, and managing data by using a columnar data format, called a columnstore.

主要詞彙和概念Key terms and concepts

以下是與資料行存放區索引相關聯的主要詞彙和概念。The following key terms and concepts are associated with columnstore indexes.

columnstoreColumnstore

資料行存放區是以邏輯方式組織成資料表的資料,其中包含資料列和資料行,並且會以資料行取向的資料格式實際儲存。A columnstore is data that's logically organized as a table with rows and columns, and physically stored in a column-wise data format.

資料列存放區Rowstore

資料列存放區是以邏輯方式組織成資料表的資料,其中包含資料列和資料行,並且會以資料列取向的資料格式實際儲存。A rowstore is data that's logically organized as a table with rows and columns, and physically stored in a row-wise data format. 此格式是傳統儲存關聯式資料表資料的方式。This format is the traditional way to store relational table data. SQL ServerSQL Server 中,資料列存放區是指其基礎資料儲存格式為堆積、叢集索引或記憶體最佳化資料表的資料表。In SQL ServerSQL Server, rowstore refers to a table where the underlying data storage format is a heap, a clustered index, or a memory-optimized table.

注意

在資料行存放區索引的相關討論中,資料列存放區和資料行存放區等詞用於強調資料儲存的格式。In discussions about columnstore indexes, the terms rowstore and columnstore are used to emphasize the format for the data storage.

資料列群組Rowgroup

資料列群組是指同時壓縮成資料行存放區格式的一組資料列。A rowgroup is a group of rows that are compressed into columnstore format at the same time. 資料列群組通常包含了每個資料列群組的資料列數目上限,即 1,048,576 個資料列。A rowgroup usually contains the maximum number of rows per rowgroup, which is 1,048,576 rows.

為達到高效能和高壓縮率,資料行存放區索引會將資料表切割為資料列群組,然後以資料行取向的方式壓縮每個資料列群組。For high performance and high compression rates, the columnstore index slices the table into rowgroups, and then compresses each rowgroup in a column-wise manner. 資料列群組中的資料列數目必須多到足以改善壓縮率,並且少到足以獲益於記憶體中作業。The number of rows in the rowgroup must be large enough to improve compression rates, and small enough to benefit from in-memory operations.

資料列群組,其中所有資料都已從 COMPRESSED 轉換成 TOMBSTONE 狀態,並在稍後由名為 Tuple Mover 的背景處理序移除。A rowgroup from where all data has been deleted transitions from COMPRESSED into TOMBSTONE state, and is later removed by a background process named the tuple-mover. 如需有關資料列群組狀態的詳細資訊,請參閱 sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)For more information about rowgroup statuses, see sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

提示

太多小型資料列群組會降低資料行存放區索引的品質。Having too many small rowgroups decreases the columnstore index quality. SQL Server 2017 (14.x)SQL Server 2017 (14.x) 之前,需要遵循內部閾值原則來進行重新組織作業,以合併較小的 COMPRESSED 資料列群組,該內部閾值原則會決定如何移除已刪除的資料列以及合併已壓縮的資料列群組。Until SQL Server 2017 (14.x)SQL Server 2017 (14.x), a reorganize operation is required to merge smaller COMPRESSED rowgroups, following an internal threshold policy that determines how to remove deleted rows and combine the compressed rowgroups.
SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始,背景合併工作也適用於合併已刪除大量資料列的 COMPRESSED 資料列群組。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), a background merge task also works to merge COMPRESSED rowgroups from where a large number of rows has been deleted.
合併較小的資料列群組之後,索引品質應該會改善。After merging smaller rowgroups, the index quality should be improved.

注意

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始,Tuple Mover 會由背景合併工作協助,該工作會自動壓縮已存在一段時間的較小 OPEN 差異資料列群組 (由內部閾值決定),或合併已刪除大量資料列的 COMPRESSED 資料列群組。Starting with SQL Server 2019 (15.x)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. 這可改善一段時間的資料行存放區索引品質。This improves the columnstore index quality over time.

資料行區段Column segment

資料行區段是指資料列群組內部的資料行。A column segment is a column of data from within the rowgroup.

  • 每一個資料列群組會針對資料表中的每一個資料行包含一個資料行區段。Each rowgroup contains one column segment for every column in the table.
  • 每個資料行區段會各自壓縮成一體並且儲存到實體媒體上。Each column segment is compressed together and stored on physical media.

資料行區段Column segment

叢集資料行存放區索引Clustered columnstore index

叢集資料行存放區索引是整個資料表的實體儲存體。A clustered columnstore index is the physical storage for the entire table.

叢集資料行存放區索引Clustered columnstore index

為降低資料行區段的分散程度並提升效能,資料行存放區索引可能會暫時將一些資料儲存到叢集索引 (稱為 差異存放區),並儲存已刪除資料列識別碼的 BTree 清單。To reduce fragmentation of the column segments and improve performance, the columnstore index might store some data temporarily into a clustered index called a deltastore and a btree list of IDs for deleted rows. 差異存放區作業將由幕後處理。The deltastore operations are handled behind the scenes. 為了能傳回正確的查詢結果,叢集資料行存放區索引會結合資料行存放區和差異存放區兩方面的查詢結果。To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.

差異資料列群組Delta rowgroup

差異資料列群組是僅能搭配資料行存放區索引使用的叢集 B 型樹狀結構索引。A delta rowgroup is a clustered B-tree index that's used only with columnstore indexes. 其可藉由儲存資料列,直到資料列數達到閾值 (1,048,576 個資料列) 後移入資料行存放區,以改善資料行存放區的壓縮與效能。It improves columnstore compression and performance by storing rows until the number of rows reaches a threshold (1,048,576 rows) and are then moved into the columnstore.

差異資料列群組一旦達到資料列數目上限,就會從 OPEN 轉換為 CLOSED 狀態。When a delta rowgroup reaches the maximum number of rows, it transitions from an OPEN to CLOSED state. 名為 Tuple Mover 的背景處理序會檢查已關閉的資料列群組。A background process named the tuple-mover checks for closed row groups. 如果處理序發現已關閉的資料列群組,便會壓縮差異資料列群組,並將其儲存至資料行存放區中作為 COMPRESSED 資料列群組。If the process finds a closed rowgroup, it compresses the delta rowgroup and stores it into the columnstore as a COMPRESSED rowgroup.

當差異資料列群組已壓縮時,現有的差異資料列群組會轉換成 TOMBSTONE 狀態,供 Tuple Mover 稍後在其沒有參考時移除。When a delta rowgroup has been compressed, the existing delta rowgroup transitions into TOMBSTONE state to be removed later by the tuple-mover when there is no reference to it.

如需有關資料列群組狀態的詳細資訊,請參閱 sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)For more information about rowgroup statuses, see sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

注意

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 開始,Tuple Mover 會由背景合併工作協助,該工作會自動壓縮已存在一段時間的較小 OPEN 差異資料列群組 (由內部閾值決定),或合併已刪除大量資料列的 COMPRESSED 資料列群組。Starting with SQL Server 2019 (15.x)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. 這可改善一段時間的資料行存放區索引品質。This improves the columnstore index quality over time.

差異存放區Deltastore

資料行存放區索引可以有多個差異資料列群組。A columnstore index can have more than one delta rowgroup. 所有差異資料列群組統稱為差異存放區。All of the delta rowgroups are collectively called the deltastore.

在大規模的大量載入過程中,大多數資料列會直接進入資料行存放區,而不經過差異存放區。During a large bulk load, most of the rows go directly to the columnstore without passing through the deltastore. 大量載入結束時,某些資料列可能因為數量太少,而不符合資料列群組 102,400 個資料列的大小下限。Some rows at the end of the bulk load might be too few in number to meet the minimum size of a rowgroup, which is 102,400 rows. 因此,最後這些資料列就會進入差異存放區,而不是資料行存放區。As a result, the final rows go to the deltastore instead of the columnstore. 若是少於 102,400 個資料列的小規模大量載入,則所有資料列都將直接進入差異存放區。For small bulk loads with less than 102,400 rows, all of the rows go directly to the deltastore.

非叢集資料行存放區索引Nonclustered columnstore index

非叢集資料行存放區索引和叢集資料行存放區索引的功能相同。A nonclustered columnstore index and a clustered columnstore index function the same. 差別在於非叢集索引是在資料列存放區資料表上建立的次要索引,但是叢集資料行存放區索引則是整個資料表的主要儲存體。The difference is that a nonclustered index is a secondary index that's created on a rowstore table, but a clustered columnstore index is the primary storage for the entire table.

非叢集索引包含基礎資料表中部分或所有資料列和資料行的複本。The nonclustered index contains a copy of part or all of the rows and columns in the underlying table. 此索引會定義為資料表的一個或多個資料行,並具有篩選資料列的選用條件。The index is defined as one or more columns of the table and has an optional condition that filters the rows.

非叢集資料行存放區索引可使用即時作業分析,其中 OLTP 工作負載會使用基礎叢集索引,並同時對資料行存放區索引執行分析。A nonclustered columnstore index enables real-time operational analytics where the OLTP workload uses the underlying clustered index while analytics run concurrently on the columnstore index. 如需詳細資訊,請參閱開始使用資料行存放區進行即時作業分析For more information, see Get started with columnstore for real-time operational analytics.

批次模式執行Batch mode execution

批次模式執行是用來同時處理多個資料列的查詢處理方法。Batch mode execution is a query processing method that's used to process multiple rows together. 批次模式執行與資料行存放區儲存格式緊密整合,並以其為中心進行最佳化。Batch mode execution is closely integrated with, and optimized around, the columnstore storage format. 批次模式執行有時又稱為 向量式向量化 執行。Batch mode execution is sometimes known as vector-based or vectorized execution. 資料行存放區索引的查詢使用批次模式執行,通常可改善查詢效能 2 至 4 倍。Queries on columnstore indexes use batch mode execution, which improves query performance typically by two to four times. 如需詳細資訊,請參閱查詢處理架構指南For more information, see the Query processing architecture guide.

為什麼應該使用資料行存放區索引?Why should I use a columnstore index?

資料行存放區索引可提供非常高度的資料壓縮,通常是 10 倍,因此可大幅降低資料倉儲儲存體成本。A columnstore index can provide a very high level of data compression, typically by 10 times, to significantly reduce your data warehouse storage cost. 資料行存放區索引在分析時所提供的效能遠比 Btree 索引還高。For analytics, a columnstore index offers an order of magnitude better performance than a btree index. 資料行存放區索引是資料倉儲和分析工作負載的慣用資料儲存格式。Columnstore indexes are the preferred data storage format for data warehousing and analytics workloads. SQL Server 2016 (13.x)SQL Server 2016 (13.x)開始,您可以使用資料行存放區索引,對您的作業工作負載進行即時分析。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can use columnstore indexes for real-time analytics on your operational workload.

資料行存放區索引之所以很快的原因︰Reasons why columnstore indexes are so fast:

  • 資料行會儲存來自相同網域的值,而且通常會有類似的值,因此壓縮率會很高。Columns store values from the same domain and commonly have similar values, which result in high compression rates. 您系統中的 I/O 瓶頸會減至最少或消失,而且會大幅減少記憶體耗用量。I/O bottlenecks in your system are minimized or eliminated, and memory footprint is reduced significantly.

  • 高壓縮率會透過使用較小的記憶體中耗用量改善查詢效能。High compression rates improve query performance by using a smaller in-memory footprint. 而查詢效能可獲得改善是因為 SQL ServerSQL Server 能夠執行更多記憶體中查詢及資料作業。In turn, query performance can improve because SQL ServerSQL Server can perform more query and data operations in memory.

  • 批次執行藉由同時處理多個資料列來改善查詢效能,通常是 2 至 4 倍。Batch execution improves query performance, typically by two to four times, by processing multiple rows together.

  • 查詢通常只會選取資料表中的少數資料行,如此可降低讀取實體媒體的總 I/O 量。Queries often select only a few columns from a table, which reduces total I/O from the physical media.

何時應該使用資料行存放區索引?When should I use a columnstore index?

建議使用案例:Recommended use cases:

如何在資料列存放區索引與資料行存放區索引之間進行選擇?How do I choose between a rowstore index and a columnstore index?

資料列存放區索引在搜尋資料的查詢、搜尋特定值,或搜尋一小段值範圍查詢的效果最佳。Rowstore indexes perform best on queries that seek into the data, when searching for a particular value, or for queries on a small range of values. 請搭配交易式工作負載使用資料列存放區索引,因為它們通常需要大量資料表搜尋,而不是資料表掃描。Use rowstore indexes with transactional workloads because they tend to require mostly table seeks instead of table scans.

資料行存放區索引可提升分析查詢的效能,現在可掃描大量資料,特別是大型資料表上的資料。Columnstore indexes give high performance gains for analytic queries that scan large amounts of data, especially on large tables. 請在資料倉儲和分析工作負載上 (尤其是在事實資料表上) 使用資料行存放區索引,因為它們通常需要完整資料表掃描,而不是資料表搜尋。Use columnstore indexes on data warehousing and analytics workloads, especially on fact tables, because they tend to require full table scans rather than table seeks.

我可以將資料列存放區和資料行存放區合併到同一個資料表嗎?Can I combine rowstore and columnstore on the same table?

是。Yes. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,您可以在資料列存放區資料表上建立可更新的非叢集資料行存放區索引。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create an updatable nonclustered columnstore index on a rowstore table. 資料行存放區索引會儲存所選資料行的複本,因此您需要額外的空間來存放此資料,但所選資料平均會壓縮 10 倍。The columnstore index stores a copy of the selected columns, so you need extra space for this data, but the selected data is compressed on average 10 times. 您就可以同時在資料行存放區索引上執行分析,並在資料列存放區索引上執行交易。You can run analytics on the columnstore index and transactions on the rowstore index at the same time. 當資料列存放區資料表中的資料變更時,會更新資料行存放區,讓兩個索引針對相同的資料執行。The columnstore is updated when data changes in the rowstore table, so both indexes work against the same data.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,您在資料行存放區索引上可以有一個或多個非叢集資料列存放區索引,而且可以針對基礎資料行存放區執行有效率的資料表搜尋。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can have one or more nonclustered rowstore indexes on a columnstore index and perform efficient table seeks on the underlying columnstore. 其他選項現在也可以使用。Other options become available too. 例如,您可以在資料列存放區資料表上使用 UNIQUE 條件約束,強制執行主索引鍵條件約束。For example, you can enforce a primary key constraint by using a UNIQUE constraint on the rowstore table. 由於非唯一值將無法插入至資料列存放區資料表,因此 SQL ServerSQL Server 無法將值插入至資料行存放區。Because a non-unique value fails to insert into the rowstore table, SQL ServerSQL Server can't insert the value into the columnstore.

中繼資料Metadata

資料行存放區索引中的所有資料行都將儲存於中繼資料內成為內含資料行。All of the columns in a columnstore index are stored in the metadata as included columns. 資料行存放區索引沒有索引鍵資料行。The columnstore index doesn't have key columns.

所有關聯式資料表都會使用資料列存放區作為基礎資料格式,除非您將其指定為叢集資料行存放區索引。All relational tables, unless you specify them as a clustered columnstore index, use rowstore as the underlying data format. 除非您指定 WITH CLUSTERED COLUMNSTORE INDEX 選項,否則 CREATE TABLE 會建立資料列存放區資料表。CREATE TABLE creates a rowstore table unless you specify the WITH CLUSTERED COLUMNSTORE INDEX option.

當您使用 CREATE TABLE 陳述式建立資料表時,可以指定 WITH CLUSTERED COLUMNSTORE INDEX 選項,將資料表建立為資料行存放區。When you create a table with the CREATE TABLE statement, you can create the table as a columnstore by specifying the WITH CLUSTERED COLUMNSTORE INDEX option. 如果您已經有一個資料列存放區資料表,並想要將它轉換成資料行存放區,則可以使用 CREATE COLUMNSTORE INDEX 陳述式。If you already have a rowstore table and want to convert it to a columnstore, you can use the CREATE COLUMNSTORE INDEX statement.

TaskTask 參考主題Reference topics 注意Notes
建立資料表作為資料行存放區。Create a table as a columnstore. CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL) SQL Server 2016 (13.x)SQL Server 2016 (13.x)開始,您可以建立資料表作為叢集資料行存放區索引。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create the table as a clustered columnstore index. 您不需要先建立資料列存放區資料表,再將它轉換成資料行存放區。You don't have to first create a rowstore table and then convert it to columnstore.
建立具有資料行存放區索引的記憶體資料表。Create a memory table with a columnstore index. CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL) SQL Server 2016 (13.x)SQL Server 2016 (13.x)開始,您可以建立具有資料行存放區索引的記憶體最佳化資料表。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create a memory-optimized table with a columnstore index. 建立資料表之後,也可以使用 ALTER TABLE ADD INDEX 語法來加入資料行存放區索引。The columnstore index can also be added after the table is created by using the ALTER TABLE ADD INDEX syntax.
將資料列存放區資料表轉換成資料行存放區。Convert a rowstore table to a columnstore. CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL) 將現有的堆積或二進位樹狀目錄轉換成資料行存放區。Convert an existing heap or binary tree to a columnstore. 範例示範如何在執行這項轉換時處理現有的索引及索引名稱。Examples show how to handle existing indexes and also the name of the index when performing this conversion.
將資料行存放區資料表轉換成資料列存放區。Convert a columnstore table to a rowstore. 建立叢集索引X (Transact-SQL)將資料行存放區資料表轉換回資料列存放區堆積CREATE CLUSTERED INDEX (Transact-SQL) or Convert a columnstore table back to a rowstore heap 此轉換通常並非必要,但有時您仍舊需要轉換。Usually this conversion isn't necessary, but there can be times when you need to convert. 範例示範如何將資料行存放區轉換成堆積或叢集索引。Examples show how to convert a columnstore to a heap or clustered index.
在資料列存放區資料表上建立資料行存放區索引。Create a columnstore index on a rowstore table. CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL) 資料列存放區資料表可以有一個資料行存放區索引。A rowstore table can have one columnstore index. SQL Server 2016 (13.x)SQL Server 2016 (13.x)開始,資料行存放區索引可以有一個篩選條件。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the columnstore index can have a filtered condition. 範例示範基本語法。Examples show the basic syntax.
為作業分析建立高效能的索引。Create performant indexes for operational analytics. 開始使用資料行存放區進行即時作業分析Get started with columnstore for real-time operational analytics 描述如何建立互補資料行存放區和 Btree 索引,讓 OLTP 查詢使用 Btree 索引,而分析查詢使用資料行存放區索引。Describes how to create complementary columnstore and btree indexes, so that OLTP queries use btree indexes and analytics queries use columnstore indexes.
為資料倉儲建立高效能的資料行存放區索引。Create performant columnstore indexes for data warehousing. 資料倉儲的資料行存放區索引Columnstore indexes for data warehousing 描述如何在資料行存放區資料表上使用 Btree 索引,建立高效能的資料倉儲查詢。Describes how to use btree indexes on columnstore tables to create performant data warehousing queries.
使用 Btree 索引,在資料行存放區索引上強制執行主索引鍵條件約束。Use a btree index to enforce a primary key constraint on a columnstore index. 資料倉儲的資料行存放區索引Columnstore indexes for data warehousing 示範如何合併 Btree 和資料行存放區索引,在資料行存放區索引上強制執行主索引鍵條件約束。Shows how to combine btree and columnstore indexes to enforce primary key constraints on the columnstore index.
卸除資料行存放區索引。Drop a columnstore index. DROP INDEX (Transact-SQL)DROP INDEX (Transact-SQL) 卸除資料行存放區索引使用 Btree 索引所使用的標準 DROP INDEX 語法。Dropping a columnstore index uses the standard DROP INDEX syntax that btree indexes use. 若卸除叢集資料行存放區索引,會將資料行存放區資料表轉換成堆積。Dropping a clustered columnstore index converts the columnstore table to a heap.
從資料行存放區索引刪除資料列。Delete a row from a columnstore index. DELETE (Transact-SQL)DELETE (Transact-SQL) 使用 DELETE (Transact-SQL) 刪除資料列。Use DELETE (Transact-SQL) to delete a row.

資料行存放區資料列SQL ServerSQL Server 會將該資料列標示為邏輯刪除,但在重建索引之前,不會回收該資料列的實體儲存體。columnstore row: SQL ServerSQL Server marks the row as logically deleted, but doesn't reclaim the physical storage for the row until the index is rebuilt.

差異存放區資料列SQL ServerSQL Server 會以邏輯方式和實際方式刪除該資料列。deltastore row: SQL ServerSQL Server logically and physically deletes the row.
更新資料行存放區索引中的資料列。Update a row in the columnstore index. UPDATE (Transact-SQL)UPDATE (Transact-SQL) 使用 UPDATE (Transact-SQL) 更新資料列。Use UPDATE (Transact-SQL) to update a row.

資料行存放區資料列SQL ServerSQL Server 會將該資料列標示為邏輯刪除,然後將更新的資料列插入差異存放區中。columnstore row: SQL ServerSQL Server marks the row as logically deleted and then inserts the updated row into the deltastore.

差異存放區資料列SQL ServerSQL Server 會更新差異存放區中的該資料列。deltastore row: SQL ServerSQL Server updates the row in the deltastore.
將資料載入資料行存放區索引。Load data into a columnstore index. 資料行存放區索引資料載入Columnstore indexes data loading
強制將差異存放區中的所有資料列移入資料行存放區。Force all rows in the deltastore to go into the columnstore. ALTER INDEX (Transact-SQL) ... REBUILDALTER INDEX (Transact-SQL) ... REBUILD

重新組織與重建索引Reorganize and Rebuild Indexes
ALTER INDEX 搭配 REBUILD 選項會強制將所有資料列移入資料行存放區。ALTER INDEX with the REBUILD option forces all rows to go into the columnstore.
重組資料行存放區索引。Defragment a columnstore index. ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL) ALTER INDEX ... REORGANIZE 會線上重組資料行存放區索引。ALTER INDEX ... REORGANIZE defragments columnstore indexes online.
合併具有資料行存放區索引的資料表。Merge tables with columnstore indexes. MERGE (Transact-SQL)MERGE (Transact-SQL)

另請參閱See also

資料行存放區索引資料載入 Columnstore indexes data loading
資料行存放區索引建立版本功能摘要 Columnstore indexes versioned feature summary
資料行存放區索引查詢效能 Columnstore indexes query performance
開始使用資料行存放區進行即時作業分析 Get started with columnstore for real-time operational analytics
資料倉儲的資料行存放區索引 Columnstore indexes for data warehousing
資料行存放區索引重組 Columnstore indexes defragmentation
SQL Server 索引設計指南 SQL Server index design guide
資料行存放區索引架構Columnstore index architecture