SQL Server 堆表行存储大小（Record Size）
一.本文所涉及的內容（Contents）
二.背景（Contexts）
有的時候你需要計算堆表的一行記錄有多大？又或者想計算一個數據頁（8K）能保存多少條記錄？字段類型是設計成nchar還是nvarchar？他們有什么區別呢？在做數據庫表設計的時候會經常出現這些問題。要計算一行記錄的大小，并不是簡單把列字段類型大小直接相加就行的，具體原因請看下文。
三.堆表行記錄存儲格式（Heap）
下面是計算堆表行記錄大小的公式，它引自MSDN：估計堆的大小
計算公式：Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 所有固定長度列的總字節大小 + 可變長度數據的大小 + Null位圖 + 數據行的行標題開銷
單看上面的計算公式是比較難理解的，而且MSDN并沒有提供相關的說明，比如上面公式最后的4代表什么意思？我參考了一些書籍之后整理出下面更容易理解的堆記錄存儲格式圖，希望能幫助大家理解：
（Figure1：堆表記錄存儲格式）
四.案例分析（Case）
本文針對堆表數據頁的存儲做一個測試，測試不同數據類型的存儲大小，測試的數據類型包括：int、char、nchar和nvarchar，測試后你會理解什么是定長類型、什么是變長類型，他們在存儲上有什么區別；
(一) 首先創建一個測試數據庫；
/******* Step1:創建示例數據庫*******/ USE master GO IF EXISTS(SELECT name FROM sys.databases WHERE name = 'RecordSize_DB') DROP DATABASE RecordSize_DB GO CREATE DATABASE RecordSize_DB GO?
(二) 接著在數據庫中創建3個不同的堆表：[HeapPage_char]、[HeapPage_nchar] 和[HeapPage_nvarchar]，3個表分別代表：char、nchar和nvarchar不同數據類型的存儲；
USE RecordSize_DB GO /******* Step2:創建個堆表*******/ CREATE TABLE [dbo].[HeapPage_char]([id] [int] IDENTITY(1,1) NOT NULL,[names] [char](10) NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[HeapPage_nchar]([id] [int] IDENTITY(1,1) NOT NULL,[names] [nchar](10) NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[HeapPage_nvarchar]([id] [int] IDENTITY(1,1) NOT NULL,[names] [nvarchar](10) NULL ) ON [PRIMARY] GO?
(三) 再接著就是在3個不同的堆表插入相同的數據；
/******* Step3:分別插入測試數據*******/ INSERT INTO [HeapPage_char](names) values('XX') GO INSERT INTO [HeapPage_char](names) values('XXXX') GO 2INSERT INTO [HeapPage_nchar](names) values('XX') GO INSERT INTO [HeapPage_nchar](names) values('XXXX') GO 2INSERT INTO [HeapPage_nvarchar](names) values('XX') GO INSERT INTO [HeapPage_nvarchar](names) values('XXXX') GO 2?
(四) 返回3個測試表數據；
-- 返回表數據 SELECT * FROM [HeapPage_char] SELECT * FROM [HeapPage_nchar] SELECT * FROM [HeapPage_nvarchar]（Figure2：HeapPage_char）
（Figure3：HeapPage_nchar）
（Figure4：HeapPage_nvarchar）
?
(五) 通過開啟3604跟蹤和DBCC PAGE命令查詢數據的存儲信息；
/******* Step4:查看各表數據頁大小*******/ --開啟跟蹤 DBCC TRACEON(3604) --查看表信息 DBCC IND(RecordSize_DB,HeapPage_char,-1)上面的命令會返回如下圖所示的信息：
（Figure5：堆表HeapPage_char）
上圖返回結果都會因為每次創建而有所不同，你需要根據每次不同的值來填寫下面的DBCC命令中的參數。上圖第一行記錄為IAM page，第二行為data page（數據頁），這里是查看數據頁的內容，在參數中填入PageFID=1和PagePID=80
--查看PAGE信息 DBCC PAGE(RecordSize_DB,1,80,1)執行上面的DBCC PAGE命令將返回下面信息：
DATA: Slot 0, Offset 0x60, Length 21, DumpStyle BYTERecord Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21Memory Dump @0x6523C06000000000: 10001200 01000000 58582020 20202020 ?........XX 00000010: 20200200 00?????????????????????????? ... Slot 1, Offset 0x75, Length 21, DumpStyle BYTERecord Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21Memory Dump @0x6523C07500000000: 10001200 02000000 58585858 20202020 ?........XXXX 00000010: 20200200 00?????????????????????????? ... Slot 2, Offset 0x8a, Length 21, DumpStyle BYTERecord Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21Memory Dump @0x6523C08A00000000: 10001200 03000000 58585858 20202020 ?........XXXX 00000010: 20200200 00?????????????????????????? ... OFFSET TABLE: Row - Offset 2 (0x2) - 138 (0x8a) 1 (0x1) - 117 (0x75) 0 (0x0) - 96 (0x60)從上面的信息我們可以知道3條記錄的長度都是：Record Size = 21，這個值是怎么算出來的呢？公式可以參考：估計堆的大小
計算公式：Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 所有固定長度列的總字節大小 + 可變長度數據的大小 + Null位圖 + 數據行的行標題開銷
計算過程：
Fixed_Data_Size = 所有固定長度列的總字節大小 = 4+10（int大小為4個字節，char(10)固定大小為10個字節）
Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
Num_Variable_Cols = 可變長度列數 = 0（沒有可變長度列）
Max_Var_Size = 所有可變長度列的最大總字節大小 = 0
Variable_Data_Size = 0（如果沒有可變長度列，請將 Variable_Data_Size 設置為 0）
Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
Num_Cols = 總列數（固定長度和可變長度）= 2（id、names兩個列）
Null_Bitmap = 3（只保留整數部分，放棄所有余數）
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 14 + 0 + 3 +4 = 21（與上面Record Size = 21符合）
?
(六) 接下來，我們來看看HeapPage_nchar表的存儲大小會有什么不同：
--查看表HeapPage_nchar DBCC ind(RecordSize_DB,HeapPage_nchar,-1)（Figure6：堆表HeapPage_nchar）
--查看PAGE信息 DBCC PAGE(RecordSize_DB,1,90,1) DATA: Slot 0, Offset 0x60, Length 31, DumpStyle BYTERecord Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 31Memory Dump @0x61ADC06000000000: 10001c00 01000000 58005800 20002000 ?........X.X. . . 00000010: 20002000 20002000 20002000 020000???? . . . . . .... Slot 1, Offset 0x7f, Length 31, DumpStyle BYTERecord Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 31Memory Dump @0x61ADC07F00000000: 10001c00 02000000 58005800 58005800 ?........X.X.X.X. 00000010: 20002000 20002000 20002000 020000???? . . . . . .... Slot 2, Offset 0x9e, Length 31, DumpStyle BYTERecord Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 31Memory Dump @0x61ADC09E00000000: 10001c00 03000000 58005800 58005800 ?........X.X.X.X. 00000010: 20002000 20002000 20002000 020000???? . . . . . .... OFFSET TABLE:Row - Offset 2 (0x2) - 158 (0x9e) 1 (0x1) - 127 (0x7f) 0 (0x0) - 96 (0x60)套用上面的公式：
計算公式：Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 所有固定長度列的總字節大小 + 可變長度數據的大小 + Null位圖 + 數據行的行標題開銷
計算過程：
Fixed_Data_Size = 所有固定長度列的總字節大小 = 4+10*2（int大小為4個字節，nchar(10)固定大小為20個字節）
Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
Num_Variable_Cols = 可變長度列數 = 0（沒有可變長度列）
Max_Var_Size = 所有可變長度列的最大總字節大小 = 0
Variable_Data_Size = 0（如果沒有可變長度列，請將 Variable_Data_Size 設置為 0）
Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
Num_Cols = 總列數（固定長度和可變長度）= 2（id、names兩個列）
Null_Bitmap = 3（只保留整數部分，放棄所有余數）
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 24 + 0 + 3 +4 = 31（與上面Record Size = 31符合）
?
總結：從上面測試結果來看：char(10)與nchar(10)是固定長度數據類型，無論你保存多少內容，在數據頁中會為記錄保留固定長度的空間，而char(10)與nchar(10)的區別是nchar(10)占用的空間是char(10)的兩倍；
?
(七) 接下來，我們來看看HeapPage_nvarchar表的存儲大小又有什么不同：
--查看表HeapPage_nvarchar DBCC ind(RecordSize_DB,HeapPage_nvarchar,-1)（Figure7：堆表HeapPage_nvarchar）
--查看PAGE信息 DBCC PAGE(RecordSize_DB,1,94,1) DATA: Slot 0, Offset 0x60, Length 19, DumpStyle BYTERecord Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS Record Size = 19 Memory Dump @0x61ADC06000000000: 30000800 01000000 02000001 00130058 ?0..............X 00000010: 005800???????????????????????????????.X. Slot 1, Offset 0x73, Length 23, DumpStyle BYTERecord Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS Record Size = 23 Memory Dump @0x61ADC07300000000: 30000800 02000000 02000001 00170058 ?0..............X 00000010: 00580058 005800??????????????????????.X.X.X. Slot 2, Offset 0x8a, Length 23, DumpStyle BYTERecord Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS Record Size = 23 Memory Dump @0x61ADC08A00000000: 30000800 03000000 02000001 00170058 ?0..............X 00000010: 00580058 005800??????????????????????.X.X.X. OFFSET TABLE:Row - Offset 2 (0x2) - 138 (0x8a) 1 (0x1) - 115 (0x73) 0 (0x0) - 96 (0x60)??
套用上面的公式：
計算公式：Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 所有固定長度列的總字節大小 + 可變長度數據的大小 + Null位圖 + 數據行的行標題開銷
計算過程：
Fixed_Data_Size = 所有固定長度列的總字節大小 = 4（int大小為4個字節）
Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
Num_Variable_Cols = 可變長度列數 = 1（names列的數據類型是nvarchar）
Max_Var_Size = 所有可變長度列的最大總字節大小 = 第一行記錄是2*2；第二、三行記錄是2*4
第一行記錄Variable_Data_Size = 2 + (1 * 2) + (2*2) = 8
第二、三行記錄Variable_Data_Size = 2 + (1 * 2) + (2*4) = 12
Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
Num_Cols = 總列數（固定長度和可變長度）= 2（id、names兩個列）
Null_Bitmap = 2 + ((2 + 7) / 8) = 3（只保留整數部分，放棄所有余數）
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
第一行記錄Row_Size = 4 + 8 + 3 + 4 = 19（與上面第一行記錄Record Size = 19符合）
第二、三行記錄Row_Size = 4 + 12 + 3 + 4 = 23（與上面第二、三行記錄Record Size = 23符合）
?
總結：從上面結果來看：nvarchar(10)是變長度數據類型，你輸入字符串有多少就存儲多少內容，這就是定長數據類型與變長數據類型的區別，從第一行記錄（占用19 Bytes）與第二、三行記錄（占用23 Bytes）占用了不同的數據空間可以證實這一點；而nchar(10)與nvarchar(10)一樣，都屬于Unicode編碼，所以占用的空間是非Unicode編碼的2倍；
五.參考文獻（References）
估計堆的大小
估計數據庫的大小
SQL SERVER單頁數據存儲行數計算
SQL Server頁中行物理存儲
SQL Server計算數據庫中表、堆、聚集索引和非聚集索引的大小
SQL Server 2008連載之存儲結構
Stairway to SQL Server Indexes（中文：SQL Server索引進階）
轉載于:https://www.cnblogs.com/gaizai/p/3431493.html
總結
以上是生活随笔為你收集整理的SQL Server 堆表行存储大小（Record Size）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 创建线程(Background Thre
- 下一篇： 时间对象