認識 資料類型：rowversion，在資料庫內自動產生唯一的二進位數字(3)

結論

資料類型：rowversion，不適合做為索引鍵 (尤其是主索引鍵) 的候選項。

可以在資料類型：rowversion上建立索引，但在異動時會產生更大磁碟 IO 負載。

說明

每次修改或插入含 rowversion 資料行的資料列時，都會在 rowversion 資料行中插入累加的資料庫資料列版本值。
這個屬性會使 rowversion 資料行不適合做為索引鍵 (尤其是主索引鍵) 的候選項。

資料列的任何更新都會變更資料列版本值，因而會變更索引鍵值。
如果資料行在主索引鍵中，舊的索引鍵值便不再有效，參考舊值的外部索引鍵也不再有效。

如果動態資料指標參考資料表，所有更新都會變更資料列在資料指標中的位置。
如果資料行在索引鍵中，資料列的所有更新也會產生索引的更新。

---

-- P01_檢視資料表的索引類型

-- P02_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊，使用 SAMPLED 模式

大量的I/D/U後，觀察：

-- P03_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊，使用 SAMPLED 模式

-- P04_觀察：rowversion資料行，有建立索引的使用特性 -- Index Seek

-- P05_觀察：rowversion資料行，沒有建立索引的使用特性 - Clustered index Scan

---

範例程式碼

-- 01_建立範例資料表：rowversion_Index1 與 rowversion_NoIndex1
USE DBRV01
GO
CREATE TABLE rowversion_Index1
(sid int IDENTITY PRIMARY KEY, pid varchar(50), rowrev rowversion)
GO

CREATE TABLE rowversion_NoIndex1
(sid int IDENTITY PRIMARY KEY, pid varchar(50), rowrev rowversion)
GO

-- 02_在範例資料表：rowversion_Index1 的 rowversion資料行上，建立索引
CREATE NONCLUSTERED INDEX [NCL01_rowversion] ON [dbo].[rowversion_Index1]
(
 [rowrev] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

--03 檢視資料表的索引類型
SELECT t.name N'資料表', i.name N'索引名稱', 
 i.type_desc N'索引類型的描述',
 i.is_unique N'索引是否唯一'
FROM sys.tables t INNER JOIN sys.indexes i
ON t.object_id = i.object_id
GO

-- 04_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊，使用 SAMPLED 模式
SELECT sch.name N'結構描述', obj.name N'資料表',
 inx.name AS N'索引名稱', 
 index_type_desc N'索引類型',
 avg_fragmentation_in_percent '片段(%)',
 avg_page_space_used_in_percent N'頁面飽和度(%)',
 fragment_count,
 avg_fragment_size_in_pages,
 page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
 INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
 INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
 INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP'  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2
GO

-- 05_Insert/Update/Delete 大量資料列，四萬筆
SET NOCOUNT ON

DECLARE @cnt INT =1

WHILE @cnt < 10001
BEGIN
 INSERT rowversion_Index1(pid) VALUES('A_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_Index1(pid) VALUES('B_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_Index1(pid) VALUES('C_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_Index1(pid) VALUES('D_'+CAST(SYSDATETIME() AS varchar(50)))

 INSERT rowversion_NoIndex1(pid) VALUES('A_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_NoIndex1(pid) VALUES('B_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_NoIndex1(pid) VALUES('C_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_NoIndex1(pid) VALUES('D_'+CAST(SYSDATETIME() AS varchar(50)))
 
 SET @cnt +=1
END
GO

-- DELETE：1萬筆
DELETE FROM rowversion_Index1
WHERE pid LIKE 'B_%'
GO
DELETE FROM rowversion_NoIndex1
WHERE pid LIKE 'B_%'
GO

-- UPDATE：1萬筆
UPDATE rowversion_Index1
SET pid = ('E_'+CAST(SYSDATETIME() AS varchar(50)))
WHERE pid LIKE 'C_%'
GO
UPDATE rowversion_NoIndex1
SET pid = ('E_'+CAST(SYSDATETIME() AS varchar(50)))
WHERE pid LIKE 'C_%'
GO

-- 05_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊，使用 SAMPLED 模式
SELECT sch.name N'結構描述', obj.name N'資料表',
 inx.name AS N'索引名稱', 
 index_type_desc N'索引類型',
 avg_fragmentation_in_percent '片段(%)',
 avg_page_space_used_in_percent N'頁面飽和度(%)',
 fragment_count,
 avg_fragment_size_in_pages,
 page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
 INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
 INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
 INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP'  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2
GO

-- 06_觀察：rowversion資料行，有建立索引的使用特性 -- Index Seek
SELECT * FROM rowversion_Index1
WHERE rowrev = 0x000000000003AD11
GO

-- 07_觀察：rowversion資料行，沒有建立索引的使用特性 - Clustered index Scan
SELECT * FROM rowversion_NoIndex1
WHERE rowrev = 0x0000000000024C20
GO

---

參考資料

認識 資料類型：rowversion，在資料庫內自動產生唯一的二進位數字(1)
http://sharedderrick.blogspot.tw/2016/03/rowversion1.html