行大小计算测试
数据库中每一行到底占用多少空间，在这里，做个小测试~~~这里只做了堆表的计算，如果包含索引，按照文档把索引的列也加上就OK啦~
建个测试表，然后用DBCC PAGE打印出Slot的信息
CREATE TABLE TstA ( col1 CHAR(5) NULL, col2 CHAR(5) NULL, col3 VARCHAR(5) NULL, col4 VARCHAR(10) NULL, col5 VARCHAR(50) NULL ) INSERT INTO TstA (col1,col2,col3,col4,col5) VALUES ('aa1','2222222222','joan2','aoaonfoen','1111111')
dbcc ind(TestDB, Object_id('TstA'),-1)
DBCC PAGE(TestDB,1,11957,1)
然后我就直接把slot的消息贴出来分析吧~~
-------------------------------------------------------------------------------------------------------------------------------------
Slot 0, Offset 0x60, Length 51, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 51
Memory Dump @0x0A93C060
00000000: 30000e00 61613120 206a6f61 6e320500 †0...aa1 joan2..
00000010: 00030023 002c0033 00323232 32323232 †...#.,.3.2222222
00000020: 32323261 6f616f6e 666f656e 31313131 †222aoaonfoen1111
00000030: 313131†††††††††††††††††††††††††††††††111
4 8 12 16 20 24 28 32 36 40 44 48 52
30000e00 61613120 206a6f61 6e320500 00030023 002c0033 00323232 3232323 32323261 6f616f6e 666f656e 31313131 313131
1 2 3 4 5 6 7 8 9
1： 固定长度列结束位置
2： 固定长度第一列(col1)
3： 固定长度第二列(col3) --如果之后还有更多的列，继续往后排
4： 行包含的栏位总数，本例为5
5： 空值维护：计算方式为 2+ (可为空列数+7)/8
6： 每一个可变列的结束位置。长度计算 2 + (可变列列数*2)
7+：变长列的每一个填充列
验证了这一句话，每行的开销
- ix bytes per row for status information and length information.
- One bit per fixed width column, rounding up to the nearest byte.
- If there are any variable length columns - four bytes for the first, plus two bytes for each additional variable length column
- An additional two bytes per row for the offset pointer located at the end of the page.
-----------------------------------------------------------------------------------------------------
经过测试，无论列的顺序怎么排，首先会先把定长的字段先排出来，然后再排变长字段~好吧~测试结束
小弟不才~欢迎大家指出不足
参考：
http://technet.microsoft.com/zh-cn/library/ms189124.aspx
PS：上面2个DBCC 的语法
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
The filenum and pagenum parameters are taken from the page IDs that come from various system tables and appear in DBCC or other system error messages. A page ID of, say, (1:354) has filenum = 1 and pagenum = 354.
The printopt parameter has the following meanings:
0 - print just the page header
1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)
2 - page header plus whole page hex dump
3 - page header plus detailed per-row interpretation
