RiCo技術農場

SQL Server2016開始，針對非叢集索引鍵長度限制已經從900 bytes放寬至1700 bytes，

當我們新增或更新資料時，由於會影響索引，所以也會觸發該機制，

但請不要忘記Disk table針對每筆資料有8060 bytes限制，

這兩種機制誰先誰後，我先簡單驗證一下

--該資料表有兩個可變長度欄位，長度分別為8000,2000
CREATE TABLE [dbo].[testvarchar](
	[c1] [int] IDENTITY(1,1) NOT NULL,
	[c2] [nvarchar](2000) NULL,
	[c3] [varchar](8000) NULL,
	[c4] [datetime] NULL
)

建立該表過程，SQL Server沒有任何警告，但當我們建立索引，我們會看到1700 bytes限制警告

create index idx1 on [testvarchar](c3,c2)

假設我新增一筆資料，我們會收到8060 長度限制錯誤

insert into [testvarchar](c2,c3,c4)
values(replicate('r',2000),replicate('r',8000),GETDATE())

或許你想和我一樣嘗試忽悠SQL Server，把長度改短，這

時我們就會收到索引鍵長度限制錯誤，如下

insert into [testvarchar](c2,c3,c4)
values(replicate('r',1000),replicate('r',6000),GETDATE())

我繼續嘗試insert then update

--insert資料，避開8060 byte和1700 bytes限制
insert into [testvarchar](c2,c3,c4)
values(replicate('r',10),replicate('r',1000),GETDATE())

當我要執行update，我依然無法突破SQL Server的防衛

update 	 [testvarchar] set c2= replicate('r',1000)

我在更深入來驗證該假設是合理又正確的

-- Retrieve physical information about the clustered index
SELECT * FROM sys.dm_db_index_physical_stats
(
    DB_ID('AdventureWorks2012_Data'),
    OBJECT_ID('testvarchar'),
    NULL,
    NULL,
    'DETAILED'
)

透過dm_db_index_physical_stats的min_record_size和max_record_size我們可以知道，index page中，

叢集和非叢集索引鍵是使用可變動長度來儲存。

 

我更進一步使用DBCC PAGE來看看更詳細資料

DBCC IND(AdventureWorks2012_Data, testvarchar, 2)

DBCC traceon(3604)
DBCC PAGE(AdventureWorks2012_Data, 1, 304768, 1)

可以看到每一筆Index Record長度都不同，另外，針對可變長度欄位上建立索引，

要了解會有額外的儲存成本2 bytes來存放可變長度的偏移量(offset)，

當我們在設計並計算一個page可存放多少index record時，也要留意這些成本，

以免無法建立良好索引來提高查詢效能。