先講重點，這裡是基礎篇，索引只會講一些基礎，怎樣把索引用的好，會在效能調教
索引有分很多種，因為這裡是基礎，所以只會講 Rowstore Index，也就是最常見的索引類型。
Columnstore Index 會在日後效能調教篇章講解但除此之外還有支援複雜型別的 index，XML、JSON、geospatial data type。這些少用未來再提，這邊會著重講解傳統 index。
另外，SQL Server 會在 index 和 table column 上維護 statistics，藉由改善 cardinality estimate 來提升查詢效率。可以讓查詢優化器建立更有效率的執行計畫，所以 statistics 也會一起討論，會在下一篇。
B-tree 是一種資料結構，可以用來組織 key value。而每一個 node 可以有超過兩個 child nodes。
這顆 tree 是平衡的，意思是要取得任一 row 資料時，所需經過的步數是相同的。
而 Clustered Index，或是你也可以叫他叢集索引，就是用 B-treee 結構，而且它會讓 table 的 data pages 依照 clustered index key 的順序進行儲存。
通常，這個 key 會是 table 的 primary key。這是典型的用法，但是在某些情況下，有可能也會想用別的欄位，等等會說。
一張 table 沒有 clustered index，就被稱作 heap。
當一張 table 以 heap 形式儲存，而且沒有 index 的時候，每次存取這張 table，SQL Server 都必須讀取 table 中的每一個 page，即使會後只回傳一筆 row 也是一樣。
當資料儲存在 heap 上時，SQL Server 需要為每一個 row 維護一個唯一識別值。他會透過建立 RID，也就是 row identifier 來做到這件事。
RID 格式 :
FileID : PageID : Slot Number
這在 filegroup 的地方有講過。
即使一張 table 有 nonclustered index，只要他沒有 clustered index，他仍然會是以 heap 的方式存。
當 nonclustered index 建立在 heap 上時，RID 會被用做 poniter，讓 nonclustered index 可以連回 base table 中正確的 row。
所以一張 heap table 如果沒有任何 index，除了一種 UPDATE 的狀況以外，其實這個 RID 就沒有什麼作用。
Heap結構
Heap table
│
├─ IAM page / IAM chain
│ └─ 記錄這個 heap 使用哪些 extents / pages
│
├─ Data page
│ ├─ row
│ ├─ row
│ └─ row
│
├─ Data page
│ ├─ row
│ ├─ row
│ └─ row
│
└─ Data page
│ ├─ row
│ ├─ row
│ └─ row
沒有按照 key 排序
彼此不靠 B-tree leaf level 順序連起來
SQL Server 主要透過 IAM page 找到它們
Heap 的優點
Heap 缺點
forwarded records 拖慢查詢，假設今天 UPDATE 了某個資料，可是這個 UPDATE 讓這個資料變大了，原本的 page 塞不下了，SQL Server 會把這個資料搬到一張新的 page，那原本存放這個資料的 page 的那個 slot，會留下一筆紀錄，這紀錄裡面包含指向新 row 位置的 RID，下一次如果 SQL Server 找資料時碰到這個被 UPDATE 的舊的位置，就會先看到 forwarding record，然後才再跳到新的位置拿真正的資料。如此多了一步跳轉浪費效能。
關於第二點有人可能會想，為什麼要這樣跳轉? 直接去掃描新的 RID 不就好了? 這個問題要回答會很深入微軟這樣設計主要原因是為了他的 nonclustered index。
nonclustered index 運作原理會放在後面。所以這邊只講為什麼需要這個設計。
SQL Server 必須維持 heap row locator 的穩定性，尤其是為了避免更新所有 nonclustered index 裡面存的 RID。
heap 沒有 clustered index，所以 heap 上的 nonclustered index leaf level 會用 RID 當 row locator。
假設原本 heap row 的 RID 是 1 : 305 : 7
那他的 nonclustered index 可能會長這樣 :
CustomerID = 100 → RID = 1 : 305 : 7
OrderDate = 2026-05-30 → RID = 1 : 305 : 7
Status = 'A' → RID = 1 : 305 : 7
那假設今天 Update 之後，row 變大，所以搬到 1 : 900 : 2
如果微軟不保留這個跳轉的設計，那他就必須去把 nonclustered index 也都改掉，改成 :
CustomerID = 100 → RID = 1 : 900 : 2
OrderDate = 2026-05-30 → RID = 1 : 900 : 2
Status = 'A' → RID = 1 : 900 : 2
如果這張 heap 有很多 nonclustered index，這個 UPDATE 的成本會變得很高。
所以微軟選擇保留原本舊的 RID 的設計。
nonclustered index 仍然指向舊 RID：1 : 305 : 7
1 : 305 : 7 放 forwarding record
forwarding record 指向真正位置：1 : 900 : 2
而它的代價就是：之後查詢如果透過舊 RID 找 row，就會多一次跳轉。
所以其實，雖然但是，如果情況允許，然後你完全對 DML 成本無所謂，想避開這種 lookup 的話，就不要用 update，改成用 delete + insert 就可以避開，但是不建議這樣做，除非真的完全沒有效能問題、這張 table 也很簡單沒有 trigger、foreign key…等等一堆問題的話。
上面是說 update 的狀況，但還有一種破碎的狀況要重建 heap table，在這種狀況下，非叢級索引就別無選擇，只能乖乖的去把 rid 都更新一遍，具體詳細說明在效能調教的篇章。
heap 是由 index allocation map page ( IAM page ) 以及一系列 data pages 組成。這些 data pages 彼此之間沒有連結，也不是依照順序儲存。
SQL Server 的資料是放在 data file 裡的，例如 .mdf / .ndf。而這些 data file 裡面被切成很多一頁 8k 的 page。
IAM page 的用途是紀錄，這個 table 或 index 到底用了哪些 extent。
他就像一張地圖，告訴 SQL Server，這個 heap 的資料頁面分布在哪些 extent 裡。
IAM page
│
├─ Header / metadata
│ ├─ 這個 IAM page 屬於哪個 allocation unit
│ ├─ 它管理哪一段 GAM interval
│ └─ 下一個 IAM page 的連結
│
└─ Bitmap (概念大概是會存1010這種東西 1 代表屬於、0不屬於)
├─ extent 100 = owned
│ ├─ page 800
│ ├─ page 801
│ ├─ page 802
│ ├─ page 803
│ ├─ page 804
│ ├─ page 805
│ ├─ page 806
│ └─ page 807
│
├─ extent 101 = not owned
│
└─ extent 102 = owned
├─ page 816
├─ page 817
├─ page 818
└─ ...
如果沒有 clustered index 也沒有 IAM page，SQL Server 會根本不知道要去哪裡拿資料。
IAM page 給了一個起碼還能找到的解決方法，起碼還可以判斷用了哪些 extent。
因為 IAM page 的特性，所以她的空間成本相對的低，他只存了一堆 01010 的東西。
IAM page 缺點 :
他只是告訴 SQL Server 整個 table 在那些 extent，但不知道要查的這筆資料是在哪一個 extent、哪一個 page、哪一筆 row，所以找到所有 extent 之後，還是得掃描整個 table 空間去找資料。
因為他沒有邏輯、沒有順序可言，所以他非常不適合範圍、排序這種查詢。
所以 SQL Server 判斷這張 heap table 有哪些 pages 的唯一方式，就是讀取 IAM page。
當我們在 table 上建立 clustered index 時，SQL Server 會建立一個 B-tree 結構。這個 B-tree 是根據 clustered key 的值建立的；
B-Tree
假設在 Orders 這張表上的 OrderID 欄位，上面建立一個 clustered index，這個 Index 名字叫做CIX_Orders_OrderID。
CREATE CLUSTERED INDEX CIX_Orders_OrderID
ON dbo.Orders(OrderID);
要了解 B-Tree 是怎麼長出來的，要先從最整棵樹的最底層也就是 leaf level 開始了解。
當在 heap 上建立 clustered index 時，SQL Server 會把原本 heap 的儲存方式重組成 B-tree 結構。這個 B-tree 的最底層，也就是 leaf level，就是實際存放資料的 data pages。資料會依照 clustered index key 的邏輯順序排列。
例如 Orders 有 80 萬筆資料，原本是 heap，所以資料會隨機亂散在不同 page 裡，沒有依照 ID 排序。如果現在用 ID 建立 clustered index，SQL Server 會依照 ID 重新組織資料，讓 leaf level 的 data pages 從最小的 ID 開始存放，page 滿了就接到下一個 leaf page。
但是這個排列，並不代表實際 MDF 檔案裡的實體 page 會連續排列，不是保證磁碟上物理位置完全連續。
事實上在建立 index 的時候，SQL Server 會自動分出 8 個 page，作為一個 extent，所以更準確的說是extent 內的 page 會連續，但是 extent 不一定會連續。
如果用文字類的去當 key，那他會依照定序去排列，在安裝的時候有講解過定序。
這是 leaf level 的上一層，介於 Root 跟 Leaf 之間。
這東西不一定會有，如果 table 很大，root page 一頁 8k 放不下那麼多 pointer 的時候，intermediate page 才會出現，他就是一個中繼點的意思。
intermediate page 存的跟 leaf level 不一樣，他會記錄的是 :ID 多少到多少在哪一個 leaf level Page
例如
OrderID 400001 ~ 499999
→ 去 Leaf Page 601
OrderID 500001 ~ 599999
→ 去 Leaf Page 602
OrderID 600001 ~ 699999
→ 去 Leaf Page 603
OrderID 700001 ~ 799999
→ 去 Leaf Page 604
這是最上層入口，他本身也是一張 page，存的東西跟 intermediate page 一魔一樣，只是範圍更大。
OrderID 400001 ~ 599999
→ 去 intermediate Page 600
OrderID 600001 ~ 799999
→ 去 intermediate Page 633
由此可以看出，整個 b-tree 是從 leaf 開始長出來的，然後每次 SQL Server，要查詢的時候，如果 where 有用到 key，那他就會從入口 root 開始找，找到之後就去標記的下一層 intermediate 開始找，在 intermediate 找到之後就再去下一層找.…一路找到最後的 leaf page 真正的資料。
intermediate 不一定只有一層，要看資料量。
舉例中的邊界點，不是 SQL Server 把資料平均切出來的。那些數字只是為了好理解，所以故意寫得很整齊。
實際上，B-tree 的邊界是由 leaf page 的資料分布、page 容量、INSERT 順序、page split 等因素自然形成的。
例如說一個 leaf page 的容量大小他只能夠放 id 1~47 的資料，
leaf page 放的是 ID 1~47，上層 intermediate page 會記錄：
Key >= 1 → Leaf Page 501
如果下一個 leaf page 放的是 ID 48~94，上層 intermediate page 會記錄：
Key >= 48 → Leaf Page 502
然後 intermediate page 如果塞滿了，就再生出一個 intermediate page 這樣子。
然後最上層的 root page 會記錄 :
Key ≥1 → intermediate Page 401
Key ≥95 → intermediate Page 402
但這個結構會產生一個問題是索引破碎，會寫在後面。
如果 clustered index 不是 unique，SQL Server 還會加入一個 uniquifier。
uniquifier 是一個用來識別 row 的值，當多筆 row 的 key value 相同時，就靠它來區分這些 row。
B-Tree 結構讓 SQL Server 可以執行 seek operation。seek 是一種非常有效率的方法，適合用來回傳少量 rows。它的運作方式是沿著 B-tree 往下走，透過 pointer 找到需要的 row。
如果有需要 ( 範圍搜尋 )，SQL Server 仍然可以 scan table 的所有 pages 來取得需要的 rows。這稱為 clustered index scan。
但還是快不過 Hash index，clustered index 是一層一層找，找到 page 後還是要掃描那 8K 的 page，但是 hash index 是算出來就直接定位 row 在哪裡了。
在這裡 hash 就會很慢，因為他要範圍內每一個點都一個一個算。
為了避免 clustered index 造成負面的效能影響，重點就是
慎選 key
最好是在一張，穩定、少變動、窄、最好唯一、最好遞增、常用於查詢的 table 上去建。
當然這是很理想的狀況，現實往往都不理想，我只能說盡量。
Clustered Index 跟 Clustered Column Index 這是兩個完全不一樣的東西。
以後只要看到沒有特別說 Column，一律都默認這是 Rowstore
再預設的情況下，除非另外指定、或是 table 上已經存在 clustered index 了，否則在 table 上建立 primary key， SQL Server 會自動在這個 key上產生 clustered index。
但是，PK 並不是 clustered index 的唯一正確選擇，還是得依照實際情況去選擇他要不要當 clustered index 的 key。
如果今天有一個情況，某個第三方的 app 要求 table 的 PK 必須是 GUID。然後因為這個原因你就把 PK 的 key 設定成 GUID，同時又用預設的方式，導致 clustered index 的 key 也是這個 GUID，這樣的話會產生兩個主要問題 :
不過第二個問題有一個解決方法，SQL Server 有一個 function 叫做 NEWSEQUENTIALID()
這個 function 會產生一個比同一台 server 上先前產生的值更大的 GUID value。因此，如果在 primary key 的 default constraint 裡使用這個 function，就可以強制進行 sequential inserts。
如果 primary key 必須是 GUID，或是其他很寬的 column，例如 身分證字號，或者 primary key 必須由一組 columns 組成自然鍵，例如 Customer ID、Order Date 和 Product ID，那麼強烈建議在 table 中建立一個額外的 column，然後把他當成普通的 ID，123456那樣，然後把 clustered index 建在這個 額外的 column 上。
有以上三種方式可以建立 clustered index
建立 index 時，可以指定許多 WITH options。
我重點只說明兩個選項一個叫做 FILLFACTOR
這是用來指定 index leaf level 的每個 page 要保留多少 free space。這可以減少 INSERT 造成的 fragmentation，但代價是 index 會變大，需要讀取更多 I/O。對 clustered index 來說，如果 key 是不會變動、持續遞增的 key，通常可以設為 0，代表 100% full，因為每一列不需要預留額外空間。
另一個叫做 : DROP_EXISTING
用來 drop 並 rebuild 現有的 index，而且使用相同名稱。
這個的主要用途是當發生破碎的時候想要重建索引，但他跟ALTER INDEX 的REBUILD功能幾乎相同，差別在DROP_EXISTING 是用在 CREATE INDEX 裡面，所以可以順便修改索引結構，如果有必要的話。我有看過有人為了解決索引碎片問題，然後用 DROP INDEX + CREATE INDEX 這樣重建，絕對不要，反正我先講絕對不要，具體原因在效能調教篇章說明。
另外，在primary key 有提到的例外狀況，有時可能會想把 clustered index 移到更適合的 column 上，尤其是當 primary key 很寬，或不是持續遞增的時候。
為了達成這件事，需要先 drop primary key constraint，然後使用 nonclusstered keyword 重新建立它。這會強制 SQL Server 使用 unique nonclustered index 來涵蓋 primary key。
完成之後，你就可以在自己選擇的 column 上建立 clustered index。
如果需要移除一個不是用來涵蓋 primary key 的 clustered index，可以使用 drop index 。
nonclustered index 跟 clustered index一樣，都是 B-tree 結構。
差別在於 :
nonclustered index 的 leaf level 不是 table 真正的 data pages，而是包含指向 table data pages 的 pointer。
也就是說 nonclustered index 的最底層不是資料本體，而是用來找到資料本體的索引資料；所以一張 table 可以有很多個 nonclustered indexes，用來支援不同的查詢效能需求。
如果查詢回傳的欄位，不包含在 nonclustered index 或 clustered index key 裡，SQL Server 就需要到 base table 找到符合條件的 rows。
這樣的話 Nonclustered index 裡面會存 CustomerID + clustered index key，等等會詳細說明為什麼，這邊只須要先知道，如果有 clustered index 的話，nonclustered index 一定會包含 clustered index 的 key。
所以目前來看 nonclustered index 裡面會存
CustomerID = 100 → OrderID = 5
CustomerID = 100 → OrderID = 9
CustomerID = 200 → OrderID = 20
因為索引裡面只有 CustomerID、OrderID 這兩個欄位，但是這個查詢還需要額外的 Amount、Note，這時候 SQL Server 就會 :
先用 nonclustered index 找到符合條件的資料
然後拿 OrderID 回 clustered index 找完整的 row，因為有說過 clustered index 的最底層其實就是真實資料。
而拿 clustered index key 回 clustered index 找完整 row 的動作，就叫做 Key Lookup
key lookup 路徑
如果 table 是 heap，那就只是把上面的所有操作，把 OrderID 的地方換成 RID 而以。
Key lookup 會很慢，因為他等於要一直來回跑
nonclustered index 找到 row 1 → 回 clustered index
nonclustered index 找到 row 2 → 回 clustered index
nonclustered index 找到 row 3 → 回 clustered index
...
這時候 SQL Server 可能就會乾脆不用 nonclustered index，直接做 scan，而 SQL Server 放棄使用 nonclustered index 的時機是根據 tipping point 決定。
通常，SQL Server 預估查詢會回傳整張 table 的 0.5%~2%以上的 rows時，他可能就不會想用 nonclustered index + key lookup 了。這邊說”可能”是因為，這是微軟優化器去決定的，所以不一定，但通常是這個範圍就會放棄用索引。
如果仔細理解上面 Key Lookup 的原理的話，就會發現，那我乾脆把需要的 column 全部都變成 nonclustered index 的 key 不就好了，這樣絕對不會發生 key lookup。
問題是這樣子做 nonclustered index 放太多 columns，index 會變得很寬、效率變得很差。
微軟為了解決這個問題，提供了 included columns 的選項。
這個他只會被包含在 index 的 leaf level；跟 key vlaues 不同。
index key values 會存在 B-tree 的每一層，而 included columns 只會存在 leaf level。
這個功能可以幫助 cover query，同時讓 index key 盡可能保持窄小；因為他避免掉原本把全部 column 都當成 key 的狀況，那樣子放就會所有 column 都在 B-tree 的每一層。
query 需要的 columns 都已經在 nonclustered index 裡，所以 SQL Server 不需要回 table 做 key lookup。
掃描一次，讀取 74 頁 page
可以看到這次的結果，跟第二步完全一樣，這是因為前面說的 tipping point，SQL Server 覺得與其用 nonclustered index 不如直接 scan 成本會更低
這次執行計畫從掃描改為搜尋，整個 I/O 成本都大幅下降，還有這次只讀取 5 頁 page，相比於前面沒有使用 index，相差非常多。
並不是每次建立 Index 都必須要整張都建，SQL Server 有提供一種只建部分 table index 的方法，就叫做 filtered index。
也因為可以部份建立的特性，所以 filtered index 只能是 nonclustered index 不能是 clustered index。
這種做法可以降低 DML operation 的 overhead，只有當 DML operation 影響到 index 中包含的資料時，index 才需要被更新。
常見的使用情境 :
如果有一張訂單表，有一千萬筆訂單資料，但是常用的查詢是”尚未出貨”，也就是 status = “尚未出貨”，而這種情況只有10萬筆。
如果建立一般的 nonclustered index，那他就會包含整張 1000 萬筆資料。但真正需要的只有那十萬筆。
所以這時候就可以建一個 index
如此的好處是
除了傳統 B-Tree 之外，SQL Server 也提供一些特殊 index ，就像開頭所說的還有 XML、JSON、geospatial data。
這些特殊 index 有協助提升針對記憶體表的 hash index，也有提供 Columnstore index ，用來提升 data warehouse 場景中的效能。
如前所述，傳統 indexes 會將資料列儲存在 data pages 上。這種方式稱為 rowstore。SQL Server 也支援 Columnstore indexes。這類 indexes 會將資料的儲存方向轉換過來，使用一個 page 來儲存某一個 column，而不是儲存一組 rows。
Segment
如果要做 columnstore index 的話，SQL Server 會先把 table 的 rows 分成一批一批，而這一批一批的東西就叫做 rowgroup。
然後，在每一個 rowgroup 裡，SQL Server 會把資料依照 column 拆開儲存。
例如 table 有三個欄位：
ID、FirstName、LastName
假設某個 rowgroup 有 1,000,000 rows，那麼這個 rowgroup 會被拆成：
ID 的 column segment
FirstName 的 column segment
LastName 的 column segment
相較於傳統 B-Tree，Columnstore index 提供更多優勢
Columnstore 實際運作原理我會把它擺在效能調教的篇章，這裡只是粗略的介紹。
不過，Columnstore indexes 並不是萬能解方。它們的設計目標是讓 data warehouse 類型、對非常大型 tables 執行 read-only operations 的 queries 達到最佳效能。OLTP 類型的 queries 不太可能從中受益，在某些情況下，甚至可能執行得更慢。
假設現在是查詢
SELECT SUM(Amount)
FROM Sales
我實際只需要 Aount 這個 column，但是在一般 rowstore 結構裡，資料都是按 row 存的，所以 SQL Server 在讀 page 的時候，會一併讀到其他不相干的欄位。
但 Columnstore 不同，他是每個 column 分開存，所以 SQL Server 可以只讀 Amount 這個欄位的 data pages，進而降低 I/O。
他可以跳過不必要的 segment。
這點原理跟 partition 很像。
columnstore 的方式前面有講過他會把 column 切成很多段，每一段都是一個 segment。
而其實這些 segment 每一個都有一個 header，用來紀錄該 segment 的 metadata。
其中最重要的就是，他會紀錄最大/最小值。
想像在優勢一的例子，假設 Sales 有一億筆資料，然後 SQL Server 在做 columnstore 的時候把 segments 切成這樣 :
| Segment | OrderDate 範圍 |
|---|---|
| Segment 1 | 2021-01-01 ~ 2021-12-31 |
| Segment 2 | 2022-01-01 ~ 2022-12-31 |
| Segment 3 | 2023-01-01 ~ 2023-12-31 |
| Segment 4 | 2024-01-01 ~ 2024-12-31 |
然後把這個例子稍作修改
SELECT SUM(Amount)
FROM Sales
WHERE OrderDate >= '2024-01-01'
這個時候 SQL Server 只要看 segment header 的 metadata 就可以知道，根本不需要前三個 segment 的資料，所以連看都不用看，如此可以增加 I/O 效能。
這就跟傳統 B-Tree 不一樣了，B-tree 可以快速訂位資料，但是 columnstore 的這種方式是直接排除不需要掃描的資料區塊。
如果是跟 clustered index 相比，乍看之下可能會很像，因為 clustered index 也是直接去找範圍起點，其他的不看。
問題是就算，他去找範圍起點，在讀取的時候，他依然是會把整個 row 都讀一遍，這地方會浪費效能。
還有，如果要這麼剛好，就是 key 要設定對，可是很多時候 key 很難說一定會是 Date，像這裡就有可能設定成 ID。
所以這也是 Columnstore 的優勢之一。
這個優化器機制，就可以看出，columnstore 的設計，基本上就是為了大型資料倉儲而誕生，一次處理1000筆跟一次處理一筆的差距顯而易見。
但是如果是 OLTP 的 table 基本就不會有什麼好處，所以 columnstore 要慎選使用環境。
這裡會有幾個東西看不懂，因為我沒有講過，第一個是optimizer怎麼產生執行計畫的?
第二個是為啥會有解壓縮跟解碼的動作?
第三個是deleted bitmap、deltastore 是什麼碗糕?
以上三點都在效能調教詳細說明
Clustered Columnstore indexes 會使整個 table 以 Columnstore 結構儲存。對於具有 clustered Columnstore index 的 table 而言，不會再有傳統的 rowstore storage；然而，新增到 table 中的新 rows 可能會暫時被放入一個 rowstore table，稱為 deltastore。這是為了避免 Columnstore index 變得 fragmented，並提升 DML operations 的效能。
讓到 deltastore 是因為，Columnstore 幾乎都是壓縮過的，如果insert、update、delete 就要重新壓縮，會很慢，所以她有一個機制是先放到 deltastore，一旦這裡累積到差不多十萬筆之後，就會開始壓縮並放入 Columnstore裡面。
十萬是一個重要數字，請牢記，在效能調教的時候會用到
當資料被 insert 到 clustered Columnstore index 時，SQL Server 會評估 rows 的數量。如果 rows 的數量足以達到良好的壓縮率，SQL Server 會將它們視為一個或多個 rowgroups，並立即將它們壓縮後加入 Columnstore index。
但如果 rows 數量太少，SQL Server 會先將它們 insert 到內部的 deltastore 結構中。當你對該 table 執行 query 時，database engine 會無縫地將這些結構結合起來，並將結果作為單一結果集回傳。
一旦 deltastore 中累積了足夠的 rows，該 deltastore 就會被標記為 closed，接著一個稱為 tuple mover 的背景程序會將這些 rows 壓縮成 Columnstore index 中的一個 rowgroup。
每個 clustered Columnstore index 可以有多個 deltastores。這是因為當 SQL Server 判斷某次 insert 適合使用 deltastore 時，它會嘗試存取既有的 deltastores。如果現在有大量同時的 insert 發生，所有既有 deltastores 都被 locked，那麼 SQL Server 會建立一個新的 deltastore，而不是強迫 query 等待 lock 被釋放。
當 clustered Columnstore index 中的一個 row 被 delete 時，該 row 只會被 logically removed。資料在實體上仍然保留在 rowgroup 中，直到下一次 index 被重新組織，或直到下一次背景執行緒執行為止。
這個背景執行緒是在 SQL Server 2019 中導入的，透過壓縮較小的 deltastores 並合併較小的 rowgroups，來提供更好的 index quality。SQL Server 會維護一個 B-tree structure，裡面存放指向 deleted rows 的 pointers，以便能夠輕易識別它們。
如果被 delete 的 row 位於 deltastore 中，而不是位於 index 本體中，那麼它會立即被刪除，包含 logical 與 physical 兩個層面。
當你 update clustered Columnstore index 中的一個 row 時，SQL Server 會將原本的 row 標記為 logically deleted，並將包含新值的新 row insert 到 deltastore 中。
建立這個 index 時，不需要指定 key column；這是因為所有 columns 都會被加入 Columnstore index 內的 column segments。接著，你的 queries 就可以使用該 index 搜尋查詢所需要的任何 column 或 columns。
Clustered Columnstore index 是該 table 上唯一的 index。你不能建立傳統的 nonclustered indexes，也不能建立 nonclustered Columnstore index。此外，該 table 也不能有 primary key、foreign key 或 unique constraints。
所以一班來說，確實是很少看到直接用 Clustered Columnstore Index的，通常都會是 Clustered Index + Nonclustered Columnstore Index
建立的語法如下
SQL Server 2022 為 clustered Columnstore indexes 導入了一項效能最佳化功能，稱為 Ordered Clustered Columnstore Indexes。這項功能會在資料被壓縮之前，先對資料進行排序，藉此提供更好的 segment elimination，進而改善效能。
Nonclustered Columnstore index 跟 Nonclustered inedx 很像，他們都不會去變更主 table 的結構。
在這裡 nonclustered columnstore index 會去針對該 index 所涵蓋的 columns 建立一份資料副本。
這表示一張 table 可以同時擁有：
很明顯地，這會增加資料所需的 storage space；但它的好處是，你可以讓 operational workloads 使用 clustered B-Tree，而讓 analytical workloads 使用 nonclustered Columnstore index。
由於 Columnstore index 通常可以達到十倍的壓縮率，因此額外增加的 storage 通常是值得的。
在寫記憶體表的時候有寫到，要建立記憶體表一定要有 index，而且記憶體表只支援兩種類型的 index。
nonclustered index 和 nonclustered hash index。
所有 in-memory indexes 都會涵蓋 table 中的所有 columns，因為它們會使用 memory pointer 連結到實際的 data row。
Memory-optimized tables 上的 indexes 必須在 CREATE TABLE statement 中建立。In-memory indexes 沒有 CREATE INDEX statement。
建立在 memory-optimized tables 上的 indexes 永遠只會儲存在 memory 中，而且不會被 persisted 到 disk，這與 table 的 durability setting 無關。當 instance restart 之後，這些 indexes 會根據 table 的底層資料重新建立。
不需要擔心 in-memory indexes 的 fragmentation，因為它們從來不具有 disk-based structure。
前面講記憶體表的時候，已經講過什麼是 hash index 了，這邊只稍微補充一點。
當許多 keys 都落在同一個 hash bucket 中時，index 的效能可能會下降，因為 SQL Server 必須掃描整條 duplicates chain，才能找到正確的 key。
因此，如果你要在一個有很多 repeated keys 的 nonunique column 上建立 hash index，你應該建立一個 bucket 數量大得多的 index。這個數量應該落在 distinct key values 數量的 20 到 100 倍，而不是像 unique indexes 通常建議的那樣，只設定為 unique keys 數量的 2 倍。
當然同一個 key 就不用說 帶入 hash function 他一定是同一個解。
同一個解就會放進同一個 bucket。
但是不同的 key 進入 hash function 運算時，也有可能得出同一個解。
這會造成，
ID = 123 算出來要放去 bucket 999
ID = 456 算出來要放去 bucket 999
ID = 789 算出來要放去 bucket 999
你下次真的去 where id = 123 的時候
你等於要把 bucket 裡面全部的東西，都掃一變，效率就低。
這種狀況不管是不是 unique 都會發生，但是 unique keys 的時候 bucket 就只建議開 2 倍的原因就是，unique 的話就只會有極少數 一起共用 bucket，比對一下其實也很快就出來了。
問題是非 unique key 的時候。
當 table 是 非 unique key ，代表一個 id 可能會有成百上千筆資料，所以就算今天key 算出的 hash 都不一樣，一個 bucket 還是得放入這麼多筆 row，遑論不同 key 但是算出同一 hash 的時候，效率會非常低，所以才會建議此時就把 bucket 擴增到 20~100倍，如此可以分散這種問題。
最後，雖然擴增是一個辦法，但是他會占用更多的 ram，而且如果今天是同一個 key 本身有太多 row 的話，這個問題依舊無法避免，這個時候就要去思考，這個 index 是不是還有存在的必要，或是要換成 nonclustered index。
這東西比較少機會接觸到，所以我實際做一次給大家看
結果可以看到，有 78% 的 BUCKETS 是空的
這百分比很高，因為在做測試資料的時候，我把 BUCKET_COUNT 設比較大，是因為把 TABLE 未來成長納入考量。
如果 empty bucket percentage 低於 33%，我們會希望指定更多 buckets，以避免 hash collisions。
也可以看到，平均 chain length 是 1，最大 chain length 是 5。這是健康的狀態。
如果 average chain count 增加，效能就會開始下降，因為 SQL Server 必須掃描多個 values，才能找到正確的 key。
如果 average chain length 達到 10 或更高，通常代表：
index key 不是 unique，而且 key 中有太多 duplicated values，導致這個 hash index 不適合。
在這種情況下，我們應該 drop 並重新建立 index，並為該 index 設定更高的 bucket count；或者更理想的做法是，改用一般的 nonclustered index。
In-memory nonclustered indexes 具有類似於 disk-based nonclustered index 的結構，稱為 Bw-tree。
這種結構使用 page-mapping table，而不是使用 pointers；而且在走訪 index 時，使用的是「小於」比較，而不是 disk-based indexes 走訪時所使用的「大於」比較。
page-mapping table & Bw-tree
Bw-tree 跟前面講的 B-tree 結構很像，他都是 root page > 中繼 page > 葉層
最大差別就是，B-tree 的 leaf level 可以雙向連結，因此可以正向與反向掃描；而 Bw-tree leaf level 是 singly linked list，只保留往下一個 leaf node 的連結，因此不能直接反向掃描。
這樣會導致 Bw-tree 沒有辦法反向掃描，
因為 A>B>C>D，在 Bw-tree 裡，A會知道下一個是B，B會知道下一個是C
但是反過來， D 不會知道他上一個是 C，同理 C 也不會知道他上一個是 B
查詢的時候去下 ORDER BY DESC的時候，效率會很差，因為SQL Server 要額外 Sort 才能知道結果。
Bw-tree 這樣做的原因是，這個 index 是為了高迸發而設計的，他希望 DML table 的時候，不要一直鎖 page。
鎖的問題還沒講過這邊先提一點
假如有一個 B-tree 結構，他的葉層資料如下
10 ⇄ 20 ⇄ 30 ⇄ 40
這幾個資料結點彼此都知道自己的上下一層是誰。
但是如果今天，要 insert 一筆新的資料 25
變成
10 ⇄ 20 ⇄ 25 ⇄ 30 ⇄ 40
這時候雙向的結構就會要去更改以下這些東西
然後又是一個高迸發的環境的話
Session A 想改 20 / 30
Session B 也想改 20 / 30
Session C 也想改附近節點
這樣子要馬被鎖到死，要馬要去做更多的同步控制。
但 Bw-tree 把這結構變成單向的
也就是說如果今天也跟前面一樣，要插入 25 這筆資料
他只需要去
這樣就好，其他都不用改，所以在高迸發的時候，就可以大幅改善鎖的問題
假如硬要說，那在改 20 的 next 還是會鎖 index 阿，這是錯的，寫這樣只是為了舉例方便看得懂，實際上的更新方式是另外一套方法。
前面說插入 25 時，Bw-tree 只需要去改 20 的 next，這只是為了方便理解，實際上不是這樣做。
實際上 Bw-tree 會用到 page-mapping table。
page-mapping table 不是存每一筆 key 的位置，也不是存 25 這筆資料的記憶體位址。它存的是「索引頁編號」對應到「這個索引頁目前的記憶體起點」。
例如原本第 200 號索引頁裡面有 10、20、30、40，page-mapping table 可能記錄：
第 200 號索引頁 → 記憶體位置 A
當插入 25 時，SQL Server 不一定會直接把原本頁面改成 10、20、25、30、40。它可能會先建立一個變更紀錄，內容是「新增 25」，然後讓 page-mapping table 改成指向這個變更紀錄的位置。
結果會變成：
第 200 號索引頁 → 記憶體位置 B
而記憶體位置 B 裡面是：
新增 25 的變更紀錄
↓
原本頁面 10、20、30、40
下次查詢時，SQL Server 還是會先走 Bw-tree index，用 key 判斷 25 應該在哪一個索引頁。找到第 200 號索引頁後，再透過 page-mapping table 找到目前的記憶體起點。接著 SQL Server 會把變更紀錄和原本頁面合起來理解，所以邏輯上看到的是 10、20、25、30、40。
如果變更紀錄累積太多，SQL Server 之後會做整理，把多個變更紀錄和原本頁面合併成一個新的乾淨索引頁，再更新 page-mapping table 指向新的索引頁位置。
這就跟 Columnstore 的處理方式很像。
當 query 使用不等式，例如 BETWEEN、> 或 < 時，nonclustered indexes 的效能會比 nonclustered hash indexes 更好。
當 query 使用 = ，但 filter 中沒有使用 index key 的所有 columns 時，In-memory nonclustered indexes 的效能也會比 nonclustered hash index 更好。
Nonclustered indexes 也可以依照 index key 的 sort order 回傳資料。
不過，和 disk-based indexes 不同的是，這些 indexes 無法依照 index key 的反向順序回傳結果。
當執行 queries 時，Database Engine 會追蹤那些它在建立 execution plan 時「想要使用」、並且可能有助於提升 query performance 的 indexes。
當你在 SSMS 中檢視 execution plan 時，會看到 missing indexes 的建議；這些資料之後也可以透過 DMVs 取得。
因為這些建議是根據單一執行計畫產生的，所以應該要先審查這些建議，不是看到建議就直接實作。
這個 Table 可以輔助我們去決定要不要建 index 跟要怎麼建 index 。
通常我會以下列幾點去判斷值得建 index :
下列幾點去判斷不要建 index
這有分兩種 internal 破碎跟 external 破碎
這是指在 page 中，存在大量的空的空間，然後 SQL Server 為了回傳某個查詢的結果，就必須要去讀更多 page，因為空的空間沒有用到所以 page 一直長，最後就是要讀越多 page。
舉例來說 :
有一張 table，有一百萬筆資料，他存進5000個 pages 中，而且所有 pages 都塞得滿滿的 100%滿的。這代表 SQL Server 只需要讀取略高於 39MB的資料就可以，8kb * 5000；
但是如果這5000張 pages 只有塞滿一半，那為了裝進等量的資料 pages 勢必要增長一被，這時候 SQL Server 變成要讀取 78 mb的 page了。
現實情況中，會發生這種事情通常都是
有執行 delete，這很自然
insert 一個不是持續遞增的 key 的時候
這是因為 SQL Server 可能會透過執行 page split 來回應這種情況。
Page split 會建立一個新的 page，將原本 page 中一半的資料移到新的 page，並將另一半資料留在原本的 page。這會導致兩個 pages 都產生 50% 的 free space。
人為設定錯誤造成，Fillfactor、PAD_INDEX 這些東西設定錯也會。
FILLFACTOR控制 index 在建立或 rebuild 時，每個 leaf level page 要保留多少 free space。
預設情況下，FILLFACTOR設為 0，意思是 page 只保留足夠剛好容納一筆 row 的空間。
但在某些情況下，如果因為 DML operations 導致大量 page splits 發生，DBA 可以透過調整FILLFACTOR來降低 fragmentation。
例如，將FILLFACTOR設為 80，表示每個 page 會保留 20% free space，讓新的 rows 可以被加入 page，而不需要發生 page split。PAD_INDEX只能在使用FILLFACTOR時套用，而且它會將相同百分比的 free space 套用到 B-tree 的 intermediate levels。
這是在指 index 的 pages 在實體儲存上變得不連續，順序混亂。導致硬碟沒辦法 sequetial read。
Internal 破碎發生狀況的第二點，也是 External 破碎發生的主因。
有兩種方式去處理這個問題 reorganizing 或 rebuild
當你 reorganize index 時，SQL Server 會重新整理 index 的 leaf level 中的資料。
它會檢查某個 page 裡是否有可用的 free space。
如果有，它會把下一個 page 中的 rows 移到這個 page 裡。
如果在這個過程結束後，有些 pages 變成空的，這些 empty pages 就會被移除。
SQL Server 只會把 pages 填到 FILLFACTOR 所指定的程度。
完成後，leaf level pages 中的資料會被重新排列，使它們的 physical order 更接近 logical key order。
Reorganizing index 永遠是 ONLINE operation。也就是說，在 reorganize 進行期間，其他 process 仍然可以使用這個 index。
因為它永遠是 ONLINE operation，所以如果 ALLOW_PAGE_LOCKS option 被關閉，reorganize 會失敗。
Reorganizing index 適合用來移除 internal fragmentation，以及程度較低、約 30% 以下 的 external fragmentation。
不過，即使在這種使用情境下，它也不能保證 operation 完成後完全沒有 fragmentation。
在效能調教的篇章我會更詳細說明破碎處理的方式，有些作法會跟這裡矛盾，原因是因為這裡寫的 30% 是微軟建議，所以我寫在基礎篇，但是當我們到效能調教的時候，會需要考慮更多情況
當你 rebuild index 時，現有的 index 會先被 drop，然後完整重新建立。
依照定義，這會移除 internal fragmentation 與 external fragmentation，因為 index 是從頭重新建立的。
不過要注意，即使 rebuild index，也不保證 operation 完成後 index 會達到 100% 無 fragmentation。
原因是 SQL Server 會把 index 的不同區塊分配給參與 rebuild 的各個 CPU core。每個 CPU core 應該會以正確順序建立自己的區段，但當這些區段同步合併時，仍可能產生少量 fragmentation。
或是去指定 MAXDOP = 1 來降低這個問題。但即使設定這個 option，在某些情況下仍可能遇到 fragmentation。
例如，如果 ALLOW_PAGE_LOCKS 被設定為 OFF，workers 會共用 allocation cache，這可能造成 fragmentation。
另外，設定 MAXDOP = 1 的代價是 index rebuild 需要更長時間。
可以使用 ONLINE 或 OFFLINE operation 來 rebuild index。
如果選擇使用 ONLINE operation rebuild index，那麼在 operation 進行期間，原本版本的 index 仍然可以被存取。
不過，ONLINE operation 的代價是需要更多時間與系統資源。
需要啟用 ALLOW_PAGE_LOCKS，才能讓 ONLINE rebuild 成功。
如果建立一個 maintenance plan 來 rebuild 或 reorganize indexes，那麼指定 database 中所有 indexes 都會被 rebuild 或 reorganize，不論它們是否真的需要。這可能很耗時，也會消耗資源。
可以透過 sys.dm_db_index_physical_stats DMF 建立一個聰明的 script，讓它透過 SQL Server Agent 執行，只針對真正需要的 indexes 進行 reorganize 或 rebuild。
建立的語法就不貼了，太長了貼不完
有一種迷思認為使用 SSD 就可以消除 index fragmentation 的問題。這是不正確的。
在 SSD 上，外部 fragmentation 通常不是應該優先處理的問題；真正值得優先處理的是內部 fragmentation。
外部破碎不處理原因是因為 SSD 本身就是隨機讀取，那你外部破碎的凌亂隨機，也對 SSD 沒有什麼影響，但沒影響不代表碎片不在，碎片依然在，只是通常都可以無視。
而內部破碎對 SSD 來說，就是絕對有影響效能的，所以要優先處理。
SQL Server 2019 支援 resumable online index creation 與 index rebuilds，適用於傳統 indexes，也就是 clustered / nonclustered indexes，以及 Columnstore indexes。
Resumable index operations 允許我暫停一個 online index operation，也就是 index build 或 rebuild，以便釋放系統資源；之後當資源使用不再是問題時，可以從先前停止的位置重新開始。
這類 operation 也允許 online index operation 在因常見原因失敗後重新啟動，例如 disk space 不足。
這項功能帶來一些明顯優點。例如，如果一個大型 index rebuild 必須塞進很短的 maintenance window，那麼我可以在 maintenance window 結束時暫停 rebuild，並在下一個 maintenance window 開始時從中斷處繼續，而不需要中止整個 operation，以釋放系統資源。
不過，它也有一些比較隱性的好處。例如，resumable index operations 即使是在大型 indexes 上執行，也不會消耗大量 transaction log space。這是因為重新啟動 index operation 所需的所有資料都會儲存在 database 中。
附帶一提，index operation 在 paused 狀態時，不會持有長時間執行的 transaction。.
大多數情況下，使用 resumable index operations 的缺點很少。
它達成的 defragmentation 品質，與標準 online index operation 相當；而且 resumable 與標準 online index operations 之間，在速度上也沒有真正差異，當然這不包含可能暫停所造成的時間。
不過，天下沒有白吃的午餐。在 paused resumable operation 期間，受影響的 tables 與 indexes 的 write performance 會下降，因為有兩個版本的 index 需要被更新。
這種下降在大多數情況下不應該超過 10%。
在 paused 期間，read operations 應該不會受到影響，因為在 operation 完成之前，讀取仍會繼續使用原本版本的 index。
除了 table 可分割外，索引也可以分割。
在分割那理以經寫過怎麼建、什麼是對齊了
這邊改說一下什麼時候會不想要 index 跟 table 的分割對齊。
table 沒有分割，但 index 可以為了某種查詢模式單獨分割。
這時候 index 當然不可能跟 table 對齊，因為 table 根本沒有 partition。
unique index 如果要跟 table partition 對齊，通常要把 partitioning key 放進 index key。
如果 unique key 不包含 partitioning key，就不能對齊。
假設用日期去當作分割的 key，然後 index 用 email 去當唯一key，index 如果也依照日期去分割，會發生 SQL Server 只能在每個 partition 裡檢查 Email 是否唯一。
例如 : 有一張會員表 Members
對這張表用 CreateYear 分割 :
Partition 1：2023 年會員
Partition 2：2024 年會員
Partition 3：2025 年會員
| MemberID | CreatedYear | |
|---|---|---|
| 1 | aaa@test.com | 2023 |
| 2 | bbb@test.com | 2024 |
| 3 | ccc@test.com | 2025 |
現在建立一個 unique index 保證 email 不重複
CREATE UNIQUE INDEX UX_Members_Email
ON dbo.Members(Email);
那如果，這個 unique index 也跟 table 一樣按照 CreateYear 去分割為了對齊，那資料會變成
Index Partition 1：2023 年的 Email
Index Partition 2：2024 年的 Email
Index Partition 3：2025 年的 Email
假設現在有兩筆資料，email 一樣，但寫進這張 table 會變成不同 partition、index 也是會變成不同 partition。
SQL Server 會變得很難去檢查唯一性，因為在 2023 年的 partition 裡 aaa@test.com 是唯一的，在 2024年裡的 partition，aaa@test.com 也是唯一的
假設有一張訂單表
這張表用 OrderDate 分割
Partition 1：2024-01
Partition 2：2024-02
Partition 3：2024-03
Partition 4：2024-04
也就是 table 的 partitioning key 是 : OrderDate
這樣設計通常是為了：
按月份管理資料
舊資料封存
只 rebuild 某個月份
查某段日期比較快
但常見查詢不是用日期，而是用 CustomerID join
但是因為為了所謂的對齊，然後就把 ID 的 Index 也故意用同樣的方式去分割，那就會造成SQL Server 要去每一個 partition 找 id :
2024-01 partition 找 CustomerID = 1001
2024-02 partition 找 CustomerID = 1001
2024-03 partition 找 CustomerID = 1001
2024-04 partition 找 CustomerID = 1001
所以這種時候如果 index 做不對齊，效率會更好。
對齊或不對齊沒有標準解，都是看當下使用方式去決定要不要對齊
iThome鐵人賽
看影片追技術