聚集索引上的列儲存索引（在聲明主鍵時創建） - SQL Server

創建聚集索引時，表本身就變成了按索引鍵排序的索引結構。

現在想像一下，我們在表 XPTO (a,b,c,d,e) 中有 5 列，並且“a”是主鍵，我們在表 XPTO 上創建了一個列儲存索引，其中包含列 (b,c)。

這個索引的結構是什麼？與聚群表相比，結構是否不同？或者列儲存是否具有指向聚集表的指針（如其他非聚集索引）。

最後，同樣的場景，但是創建具有所有屬性的列索引，結構是什麼？

通過創建測試腳本，我們可以學到很多東西！您應該能夠在任何 SQL 2014 實例上執行下面的 SQL（也可能是 SQL 2012，但我沒有在那裡進行測試）。

從這個腳本中，我們可以看到 (b, c) 上的非聚集列儲存索引確實儲存聚集索引列 (a) 的數據，並且它以與儲存 b 和 c 相同的方式（通過段）儲存數據. 這允許列儲存有效地處理訪問 a、b 和 c 的查詢。從技術上講，它甚至允許將列儲存與鍵查找結合使用，以處理需要表中所有列的查詢（儘管考慮到鍵查找）。

關於您關於列儲存索引的“結構”的問題，我認為這個問題太深了，無法在這裡回答。但是，如果您有興趣了解更多資訊，我認為 Niko Neugebauer 在 columnstore 上出色的 55 部分（並且還在不斷增長）系列中包含大量有關結構和內部結構的寶貴資訊：http: //www.nikoport.com/columnstore/

-- Create a table to your specs
CREATE TABLE dbo.XPTO (a BIGINT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL, e INT NOT NULL, CONSTRAINT PK_XPTO PRIMARY KEY (a))
GO

-- Insert some trivial dummy data
INSERT INTO dbo.XPTO (a, b, c, d, e)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)), v2.x, v3.x, v4.x, v5.x
FROM ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v2(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v3(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v4(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v5(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v6(x)
GO

-- Create the nonclustered columnstore
CREATE NONCLUSTERED COLUMNSTORE INDEX cs_XPTO ON dbo.XPTO (b, c)
GO

-- Turn on "Include Actual Execution Plan"

-- Access only the columns specified in the columnstore index
-- Here we get a Columnstore Index Scan, as expected
SELECT SUM(b), SUM(c)
FROM XPTO
GO

-- Add in the clustered index column
-- Now we also get a Columnstore Index Scan!
-- This shows that the non-clustered columnstore does maintain data for "a"
-- because it is able to process this query without touching the clustered index
SELECT SUM(a), SUM(b), SUM(c)
FROM XPTO
GO

-- Access all columns
-- Now we get a Clustered Index Scan, and the columnstore index is not used
SELECT SUM(a), SUM(b), SUM(c), SUM(d), SUM(e)
FROM XPTO
GO

-- Try to force usage of columnstore
-- Now we see a tplan that uses the columnstore index but performs a key looked up to the clustered index
-- This is a plan shape I have never seen the query optmizer generate on its own, but it makes sense
-- that it is possible given that the columnstore index stores data for the clustered index column,
-- which is essentially a pointer to the corresponding row in the table
SELECT SUM(a), SUM(b), SUM(c), SUM(d), SUM(e)
FROM XPTO WITH(INDEX(cs_XPTO))
GO

-- View the column store segments
-- Here we see that there is a column store segment for columns a, b, and c (even though we asked for just b and c!)
-- This seems to indicate that clustered index columns are implicitly added to any nonclustered columnstore index
-- and are stored within that columnstore index in the same way as the requested columns
SELECT cs.*
FROM sys.partitions p
JOIN sys.column_store_segments cs
   ON cs.partition_id = p.partition_id
WHERE p.object_id = OBJECT_ID('XPTO')
GO

-- Cleanup
DROP TABLE dbo.XPTO
GO

引用自：https://dba.stackexchange.com/questions/103722