在SQL Server中，數(shù)據(jù)的存儲(chǔ)以頁(yè)為單位。八個(gè)頁(yè)為一個(gè)區(qū)。一頁(yè)為8K，一個(gè)區(qū)為64K，這個(gè)意味著1M的空間可以容納16個(gè)區(qū)。如圖1所示：
如圖1（PS：發(fā)現(xiàn)用windows自帶的畫(huà)圖程序畫(huà)博客中的圖片也不錯(cuò)）可以看出，SQL Server中的分配單元分為三種，分別為存儲(chǔ)行內(nèi)數(shù)據(jù)的In_Row_Data，存儲(chǔ)Lob對(duì)象的LOB_Data，存儲(chǔ)溢出數(shù)據(jù)的Row_Overflow_data。下面我們通過(guò)一個(gè)更具體的例子來(lái)理解這三種分配單元。
除去IAM頁(yè)，這1行數(shù)據(jù)所需要三個(gè)頁(yè)來(lái)存儲(chǔ)。首先是LOB頁(yè)，這類(lèi)是用于存儲(chǔ)存在數(shù)據(jù)庫(kù)的二進(jìn)制文件所設(shè)計(jì)，當(dāng)這個(gè)類(lèi)型的列出現(xiàn)時(shí)，在原有的列會(huì)存儲(chǔ)一個(gè)24字節(jié)的指針，而將具體的二進(jìn)制數(shù)據(jù)存在LOB頁(yè)中，除去Text之外，VarBinary（max）也是存在LOB頁(yè)中的。然后是溢出行，在SQL Server 2000中，一行超過(guò)8060字節(jié)是不被允許的，在SQL Server 2005之后的版本對(duì)這個(gè)特性進(jìn)行了改進(jìn)，使用Varchar，nvarchar等數(shù)據(jù)類(lèi)型時(shí)，當(dāng)行的大小不超過(guò)8060字節(jié)時(shí)，全部存在行內(nèi)In-row data，當(dāng)varchar中存儲(chǔ)的數(shù)據(jù)過(guò)多使得整行超過(guò)8060字節(jié)時(shí)，會(huì)將額外的部分存于Row-overflow data頁(yè)中，如果update這列使得行大小減少到小于8060字節(jié)，則這行又會(huì)全部回到in-row data頁(yè)。
數(shù)據(jù)類(lèi)型的選擇
在了解了一些基礎(chǔ)知識(shí)之后。我們知道SQL Server讀取數(shù)據(jù)是以頁(yè)為單位，更少的頁(yè)不僅僅意味著更少的IO，還有更少的內(nèi)存和CPU資源消耗。所以對(duì)于數(shù)據(jù)選擇的主旨是：
盡量使得每行的大小更小
這個(gè)聽(tīng)起來(lái)非常簡(jiǎn)單，但實(shí)際上還需要對(duì)SQL Server的數(shù)據(jù)類(lèi)型有更多的了解。
比如存儲(chǔ)INT類(lèi)型的數(shù)據(jù)，按照業(yè)務(wù)規(guī)則，能用INT就不用BIGINT，能用SMALLINT就不用INT，能用TINYINT就不用SMALLINT。
所以為了使每行的數(shù)據(jù)更小，則使用占字節(jié)最小的數(shù)據(jù)類(lèi)型。
1、比如不要使用DateTime類(lèi)型，而根據(jù)業(yè)務(wù)使用更精確的類(lèi)型，如下表：
2、使用VarChar（Max），Nvarchar（Max），varbinary（Max）來(lái)代替text，ntext和image類(lèi)型
根據(jù)前面的基礎(chǔ)知識(shí)可以知道，對(duì)于text，ntext和image類(lèi)型來(lái)說(shuō)，每一列只要不為null，即使占用很小的數(shù)據(jù)，也需要額外分配一個(gè)LOB頁(yè)，這無(wú)疑占用了更多的頁(yè)。而對(duì)于Varchar（Max）等數(shù)據(jù)類(lèi)型來(lái)說(shuō)，當(dāng)數(shù)據(jù)量很小的時(shí)候，存在In-row-data中就能滿足要求，而不用額外的LOB頁(yè)，只有當(dāng)數(shù)據(jù)溢出時(shí)，才會(huì)額外分配LOB頁(yè)，除此之外，Varchar（Max）等類(lèi)型支持字符串操作函數(shù)比如：
● COL_LENGTH
● CHARINDEX
● PATINDEX
● LEN
● DATALENGTH
● SUBSTRING
3、對(duì)于僅僅存儲(chǔ)數(shù)字的列，使用數(shù)字類(lèi)型而不是Varchar等。
因?yàn)閿?shù)字類(lèi)型占用更小的存儲(chǔ)空間。比如存儲(chǔ)123456789使用INT類(lèi)型只需要4個(gè)字節(jié)，而使用Varchar就需要9個(gè)字節(jié)（這還不包括Varchar還需要占用4個(gè)字節(jié)記錄長(zhǎng)度）。
4、如果沒(méi)有必要，不要使用Nvarchar，Nchar等以“字”為單位存儲(chǔ)的數(shù)據(jù)類(lèi)型。這類(lèi)數(shù)據(jù)類(lèi)型相比varchar或是char需要更多的存儲(chǔ)空間。
5、關(guān)于Char和VarChar的選擇
這類(lèi)比較其實(shí)有一些了。如果懶得記憶，大多數(shù)情況下使用Varchar都是正確的選擇。我們知道Varchar所占用的存儲(chǔ)空間由其存儲(chǔ)的內(nèi)容決定，而Char所占用的存儲(chǔ)空間由定義其的長(zhǎng)度決定。因此Char的長(zhǎng)度無(wú)論存儲(chǔ)多少數(shù)據(jù)，都會(huì)占用其定義的空間。所以如果列存儲(chǔ)著像郵政編碼這樣的固定長(zhǎng)度的數(shù)據(jù)，選擇Char吧，否則選擇Varchar會(huì)比較好。除此之外，Varchar相比Char要多占用幾個(gè)字節(jié)存儲(chǔ)其長(zhǎng)度，下面我們來(lái)做個(gè)簡(jiǎn)單的實(shí)驗(yàn)。
首先我們建立表，這個(gè)表中只有兩個(gè)列，一個(gè)INT類(lèi)型的列，另一個(gè)類(lèi)型定義為Char（5），向其中插入兩條測(cè)試數(shù)據(jù)，然后通過(guò)DBCC PAGE來(lái)查看其頁(yè)內(nèi)結(jié)構(gòu)，如圖4所示。
圖4.使用char(5)類(lèi)型，每行所占的空間為16字節(jié)
下面我們?cè)賮?lái)看改為Varchar(5)，此時(shí)的頁(yè)信息，如圖5所示。
圖5.Varchar(5)，每行所占用的空間為20字節(jié)
因此可以看出，Varchar需要額外4個(gè)字節(jié)來(lái)記錄其內(nèi)容長(zhǎng)度。因此，當(dāng)實(shí)際列存儲(chǔ)的內(nèi)容長(zhǎng)度小于5字節(jié)時(shí)，使用char而不是varchar會(huì)更節(jié)省空間。
關(guān)于Null的使用
關(guān)于Null的使用也是略有爭(zhēng)議。有些人建議不要允許Null，全部設(shè)置成Not Null+Default。這樣做是由于SQL Server比較時(shí)就不會(huì)使用三值邏輯（TRUE，F(xiàn)ALSE，UNKNOWN），而使用二值邏輯（True，F(xiàn)alse），并且查詢的時(shí)候也不再需要IsNull函數(shù)來(lái)替換Null值。
但這也引出了一些問(wèn)題，比如聚合函數(shù)的時(shí)候，Null值是不參與運(yùn)算的，而使用Not Null+Default這個(gè)值就需要做排除處理。
因此Null的使用還需要按照具體的業(yè)務(wù)來(lái)看。
考慮使用稀疏列（Sparse）
稀疏列是對(duì) Null 值采用優(yōu)化的存儲(chǔ)方式的普通列。 稀疏列減少了 Null 值的空間需求，但代價(jià)是檢索非 Null 值的開(kāi)銷(xiāo)增加。 當(dāng)至少能夠節(jié)省 20% 到 40% 的空間時(shí)，才應(yīng)考慮使用稀疏列。
稀疏列在SSMS中的設(shè)置如圖6所示。
圖6.稀疏列
對(duì)于主鍵的選擇
對(duì)于主鍵的選擇是表設(shè)計(jì)的重中之重，因?yàn)橹麈I不僅關(guān)系到業(yè)務(wù)模型，更關(guān)系到對(duì)表數(shù)據(jù)操作的的效率（因?yàn)橹麈I會(huì)處于B樹(shù)的非葉子節(jié)點(diǎn)中，對(duì)樹(shù)的高度的影響最多）。關(guān)于主鍵的選擇，我之前已經(jīng)有一篇文章關(guān)于這點(diǎn)：從性能的角度談SQL Server聚集索引鍵的選擇，這里就不再細(xì)說(shuō)了。
總結(jié)
本篇文章對(duì)于設(shè)計(jì)表時(shí)，數(shù)據(jù)列的選擇進(jìn)行了一些探尋。好的表設(shè)計(jì)不僅僅是能滿足業(yè)務(wù)需求，還能夠滿足對(duì)性能的優(yōu)化。