[SQL Server] 1,700 bytes and 32 columns for nonclustered index

SQL Server 2016 (13.x) 與 Azure SQL Database 增強 Nonclustered Index(非叢集索引)：

• 資料長度延展為 1,700 Bytes，由 32 個 Column 組成。

先前版本是：

• 資料長度為 900 Bytes，由 16 個 Column 組成。

Clustered Index(叢集索引) 仍是 900 Bytes。

若有需要超過，請改用 Included Columns (內含資料行的索引)

---

[SQL Server] 1,700 bytes and 32 columns for nonclustered index


01. 建立資料表: NonCL_Size

• NCL VARCHAR(2048)

並建立 NonClustered Index

-- figure 11_Create NonClustered Index_Maximum_1700

Warning! The maximum key length for a nonclustered index is 1700 bytes. 
The index 'ix_NCL' has maximum length of 2048 bytes. 

For some combination of large values, the insert/update operation will fail.


02. 輸入資料:

• 第 1 筆，資料長度是 1700 bytes，沒有超過 Nonclustered Indexes 上限。
• 第 2 筆，資料長度是 1701 bytes，超過 Nonclustered Indexes 上限，導致新增失敗。

-- figure 12_Operation failed_over_1700

Msg 1946, Level 16, State 3, Line 32
Operation failed. 

The index entry of length 1701 bytes for the index 'ix_NCL' exceeds the maximum length of 1700 bytes for nonclustered indexes.


03. 查詢資料表，確認僅 第 1 筆 輸入成功。


-- figure 13_Only 1 row

04.  建立資料表: CL_Size

• CL VARCHAR(1024)

建立 Clustered Index

-- figure 21_Create Clustered Index_Maximum_900

Warning! The maximum key length for a clustered index is 900 bytes. 
The index 'ix_CL' has maximum length of 1024 bytes. 

For some combination of large values, the insert/update operation will fail.


05. 輸入資料:


第 1 筆，資料長度是 900 bytes，沒有超過 Clustered Indexes 上限。
第 2 筆，資料長度是 901 bytes，超過 Clustered Indexes 上限，導致新增失敗。


-- figure 22_Operation failed_over_900

Msg 1946, Level 16, State 3, Line 72
Operation failed. 

The index entry of length 901 bytes for the index 'ix_CL' exceeds the maximum length of 900 bytes for clustered indexes.


06. 查詢資料表，確認僅 第 1 筆 輸入成功。

-- figure 23_Only 1 row

---

SQL Server 2016 與 Azure SQL Database 增強 nonclustered index(非叢集索引)，將資料長度延展為 1,700 Bytes，先前版本是 900 Bytes。

Clustered Index(叢集索引)為 900 個位元組數。 Nonclustered Index(非叢集索引) 為 1,700 Bytes。

您可以使用大小上限增加超過限制的可變長度資料行，來定義索引鍵。
不過，這些資料行中的資料大小總和不得超過限制。

在非叢集索引中，您可以包含額外的非索引鍵資料行，這些資料行不會計入索引鍵的大小限制。
非索引鍵資料行可能有助於提升某些查詢的執行效能。

---

Sample Code


20180712_1700byte_32column_Nonclustered
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

Reference

Maximum Capacity Specifications for SQL Server
https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2017

Increased nonclustered index key size with SQL Server 2016
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/04/26/increased-nonclustered-index-key-size-with-sql-server-2016/

Create Indexes with Included Columns
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017