SQL Server每個(gè)表中各列的數(shù)據(jù)類型的有各種形式，產(chǎn)生的效果也各有不同，我們主要根據(jù)效率兼顧性能的情況下討論下如何規(guī)定類型。

在SQL Server中，數(shù)據(jù)的存儲(chǔ)以頁(yè)為單位。八個(gè)頁(yè)為一個(gè)區(qū)。一頁(yè)為8K，一個(gè)區(qū)為64K,這個(gè)意味著1M的空間可以容納16個(gè)區(qū)。 SQL Server中的分配單元分為三種，分別為存儲(chǔ)行內(nèi)數(shù)據(jù)的In_Row_Data，存儲(chǔ)Lob對(duì)象的LOB_Data,存儲(chǔ)溢出數(shù)據(jù)的Row_Overflow_data。下面我們通過一個(gè)更具體的例子來理解這三種分配單元。

我建立如圖2所示的表。

圖2.測(cè)試表

圖2的測(cè)試表不難看出，通過插入數(shù)據(jù)使得每一行的長(zhǎng)度會(huì)超過每頁(yè)所能容納的最大長(zhǎng)度8060字節(jié)。使得不僅產(chǎn)生了行溢出(Row_Overflow_Data)，還需要存儲(chǔ)LOB的頁(yè).測(cè)試的插入語(yǔ)句和通過DBCC IND看到的分配情況如圖3所示。

圖3.超過8060字節(jié)的行所分配的頁(yè)

除去IAM頁(yè)，這1行數(shù)據(jù)所需要三個(gè)頁(yè)來存儲(chǔ)。首先是LOB頁(yè)，這類是用于存儲(chǔ)存在數(shù)據(jù)庫(kù)的二進(jìn)制文件所設(shè)計(jì)，當(dāng)這個(gè)類型的列出現(xiàn)時(shí)，在原有的列會(huì)存儲(chǔ)一個(gè)24字節(jié)的指針，而將具體的二進(jìn)制數(shù)據(jù)存在LOB頁(yè)中，除去Text之外，VarBinary(max)也是存在LOB頁(yè)中的。然后是溢出行，在SQL Server 2000中，一行超過8060字節(jié)是不被允許的，在SQL Server 2005之后的版本對(duì)這個(gè)特性進(jìn)行了改進(jìn)，使用Varchar,nvarchar等數(shù)據(jù)類型時(shí)，當(dāng)行的大小不超過8060字節(jié)時(shí)，全部存在行內(nèi)In-row data,當(dāng)varchar中存儲(chǔ)的數(shù)據(jù)過多使得整行超過8060字節(jié)時(shí)，會(huì)將額外的部分存于Row-overflow data頁(yè)中，如果update這列使得行大小減少到小于8060字節(jié)，則這行又會(huì)全部回到in-row data頁(yè)。

在了解了一些基礎(chǔ)知識(shí)之后。我們知道SQL Server讀取數(shù)據(jù)是以頁(yè)為單位，更少的頁(yè)不僅僅意味著更少的IO，還有更少的內(nèi)存和CPU資源消耗。所以對(duì)于數(shù)據(jù)選擇的主旨是:

盡量使得每行的大小更小

這個(gè)聽起來非常簡(jiǎn)單，但實(shí)際上還需要對(duì)SQL Server的數(shù)據(jù)類型有更多的了解。

比如存儲(chǔ)INT類型的數(shù)據(jù)，按照業(yè)務(wù)規(guī)則，能用INT就不用BIGINT,能用SMALLINT就不用INT,能用TINYINT就不用SMALLINT。

所以為了使每行的數(shù)據(jù)更小，則使用占字節(jié)最小的數(shù)據(jù)類型。

1.比如不要使用DateTime類型，而根據(jù)業(yè)務(wù)使用更精確的類型，如下表:

類型 所占字節(jié)

Date(僅日期) 3

Time(僅時(shí)間) 5

DateTime2(時(shí)間和日期) 8

DateTimeOffSet(外加時(shí)區(qū)） 10

2.使用VarChar(Max),Nvarchar(Max),varbinary(Max)來代替text,ntext和image類型

根據(jù)前面的基礎(chǔ)知識(shí)可以知道，對(duì)于text,ntext和image類型來說，每一列只要不為null,即使占用很小的數(shù)據(jù)，也需要額外分配一個(gè)LOB頁(yè)，這無疑占用了更多的頁(yè)。而對(duì)于Varchar(Max)等數(shù)據(jù)類型來說，當(dāng)數(shù)據(jù)量很小的時(shí)候，存在In-row-data中就能滿足要求，而不用額外的LOB頁(yè)，只有當(dāng)數(shù)據(jù)溢出時(shí)，才會(huì)額外分配LOB頁(yè)，除此之外，Varchar(Max)等類型支持字符串操作函數(shù)比如：

• COL_LENGTH
• CHARINDEX
• PATINDEX
• LEN
• DATALENGTH
• SUBSTRING

3.對(duì)于僅僅存儲(chǔ)數(shù)字的列，使用數(shù)字類型而不是Varchar等。

因?yàn)閿?shù)字類型占用更小的存儲(chǔ)空間。比如存儲(chǔ)123456789使用INT類型只需要4個(gè)字節(jié)，而使用Varchar就需要9個(gè)字節(jié)(這還不包括Varchar還需要占用4個(gè)字節(jié)記錄長(zhǎng)度)。

4.如果沒有必要，不要使用Nvarchar,Nchar等以“字”為單位存儲(chǔ)的數(shù)據(jù)類型。這類數(shù)據(jù)類型相比varchar或是char需要更多的存儲(chǔ)空間。

5.關(guān)于Char和VarChar的選擇

這類比較其實(shí)有一些了。如果懶得記憶，大多數(shù)情況下使用Varchar都是正確的選擇。我們知道Varchar所占用的存儲(chǔ)空間由其存儲(chǔ)的內(nèi)容決定，而Char所占用的存儲(chǔ)空間由定義其的長(zhǎng)度決定。因此Char的長(zhǎng)度無論存儲(chǔ)多少數(shù)據(jù)，都會(huì)占用其定義的空間。所以如果列存儲(chǔ)著像郵政編碼這樣的固定長(zhǎng)度的數(shù)據(jù)，選擇Char吧，否則選擇Varchar會(huì)比較好。除此之外，Varchar相比Char要多占用幾個(gè)字節(jié)存儲(chǔ)其長(zhǎng)度，下面我們來做個(gè)簡(jiǎn)單的實(shí)驗(yàn)。

首先我們建立表，這個(gè)表中只有兩個(gè)列，一個(gè)INT類型的列，另一個(gè)類型定義為Char(5)，向其中插入兩條測(cè)試數(shù)據(jù)，然后通過DBCC PAGE來查看其頁(yè)內(nèi)結(jié)構(gòu)，如圖4所示。

圖4.使用char(5)類型，每行所占的空間為16字節(jié)

下面我們?cè)賮砜锤臑閂archar(5),此時(shí)的頁(yè)信息，如圖5所示。

圖5.Varchar(5)，每行所占用的空間為20字節(jié)

因此可以看出，Varchar需要額外4個(gè)字節(jié)來記錄其內(nèi)容長(zhǎng)度。因此，當(dāng)實(shí)際列存儲(chǔ)的內(nèi)容長(zhǎng)度小于5字節(jié)時(shí)，使用char而不是varchar會(huì)更節(jié)省空間。

關(guān)于Null的使用也是略有爭(zhēng)議。有些人建議不要允許Null,全部設(shè)置成Not Null+Default。這樣做是由于SQL Server比較時(shí)就不會(huì)使用三值邏輯(TRUE,FALSE,UNKNOWN)，而使用二值邏輯(True,False),并且查詢的時(shí)候也不再需要IsNull函數(shù)來替換Null值。

但這也引出了一些問題，比如聚合函數(shù)的時(shí)候,Null值是不參與運(yùn)算的，而使用Not Null+Default這個(gè)值就需要做排除處理。

因此Null的使用還需要按照具體的業(yè)務(wù)來看。

稀疏列是對(duì) Null 值采用優(yōu)化的存儲(chǔ)方式的普通列。 稀疏列減少了 Null 值的空間需求，但代價(jià)是檢索非 Null 值的開銷增加。 當(dāng)至少能夠節(jié)省 20% 到 40% 的空間時(shí)，才應(yīng)考慮使用稀疏列。

稀疏列在SSMS中的設(shè)置如圖6所示。

圖6.稀疏列

更具體的稀疏列如何能節(jié)省空間，請(qǐng)參看MSDN。

對(duì)于主鍵的選擇是表設(shè)計(jì)的重中之重，因?yàn)橹麈I不僅關(guān)系到業(yè)務(wù)模型，更關(guān)系到對(duì)表數(shù)據(jù)操作的的效率（因?yàn)橹麈I會(huì)處于B樹的非葉子節(jié)點(diǎn)中，對(duì)樹的高度的影響最多）。這個(gè)我們得結(jié)合主鍵索引的選擇來具體分析，之前寫過一篇關(guān)于索引的，以后有需要再進(jìn)一步延伸來講

總結(jié)

本篇文章對(duì)于設(shè)計(jì)表時(shí)，數(shù)據(jù)列的選擇進(jìn)行了一些探尋。好的表設(shè)計(jì)不僅僅是能滿足業(yè)務(wù)需求，還能夠滿足對(duì)性能的優(yōu)化。