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

結論

在既有的資料表上，新增加資料行，使用資料類型：rowversion，這會當下耗用磁碟I/O資源。

解決方向：

(1) 請安排於 系統離峰時間 建置。
(2) 搭配 高速儲存設備(例如：Flash)來縮短所需的建置時間!

---

-- P01_查詢目前資料庫內，每一個資料表的使用空間資訊

-- P02_新增 資料行 rowversion，觀察耗費的時間

-- P03_觀察 資料表

-- P04_已經新增資料行rowversion_SSMS

---

範例程式碼

-- 01_查詢目前資料庫內，每一個資料表的使用空間資訊
USE TSIO
GO
SELECT a3.name AS N'結構描述', a2.name AS N'資料表', a1.rows AS N'資料列筆數', (a1.reserved + ISNULL(a4.reserved,0))* 8.0/1024 AS N'配置的空間總量(MB)', a1.data * 8.0/1024 AS '資料(MB)', 
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0/1024 AS N'索引(MB)', 
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0/1024 AS N'尚未使用(MB)',
a2.create_date N'建立日期', a2.modify_date N'修改日期'
FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], 
SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) 
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, 
SUM (ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps 
GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN
(SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) 
WHERE it.internal_type IN (202,204) 
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) 
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) 
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a1.data DESC
GO

-- 02_新增 資料行 rowversion，觀察耗費的時間
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
GO

ALTER TABLE TS_PK_2GB
 ADD vercol rowversion;
GO

-- 03_觀察 資料表
SELECT TOP 100 * FROM TS_PK_2GB
GO

---

參考資料

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