Design6：選擇合適的數據類型

資料庫使用Table來存儲海量的數據，細分Table結構，數據最終存儲在Table Column中，因此，在設計Table Schema時，必須慎重選擇Table Column的Data Type，數據類型不僅決定了Column能夠存儲的數據範圍和能夠進行的操作，而且合適的數據類型還能提高查詢和修改數據的性能。數據類型的選擇標準既要滿足業務和擴展性的需求，又要使行寬最小（行寬是一行中所有column占用的Byte）。最佳實踐：使用與Column最大值最接近的數據類型。

例如，bit 類型只能存儲1和0，能夠對bit進行邏輯比較（=或<>），不能對進行算術運算（+,-,*,/,%），不要對其進行>或<的比較，雖然bit類型支持，但是，這不 make sense。

declare @b1 bit
declare @b2 bit 

set @b1=1
set @b2=0

-- right,return 0
select iif(@b1=@b2,1,0)

--error,The data types bit and bit are incompatible in the add operator.
select @b1+@b2

在設計Table Schema時，要實現三大目標：占用空間少，查詢速度快，更新速度快。這三個目標有些千絲萬縷的關聯，設計良好的Table Schema，都會實現，反之，設計差的Table Schema，都不能實現。

記憶體是訪問速度最快的存儲介質，如果數據全部存儲在記憶體中，那會極大的提高資料庫系統的吞吐量，但是，每個資料庫系統能夠使用的記憶體有限，為了提高查詢性能，SQL Server將最近使用過的數據駐留在記憶體中。SQL Server 查詢的數據必須在記憶體中，如果目標數據頁不在記憶體中，那麼SQL Server會將數據從Disk讀取到記憶體中。SQL Server 響應時間跟數據載入很大的關係，如果載入的數據集占用的空間小，數據頁分佈集中，那麼SQL Server使用預讀機制，能夠很快將數據載入到記憶體，相應地，SQL Server的響應時間會很小。

創建索引能夠提高查詢性能，其實是因為，索引欄位比Base Table的欄位少，索引結構占用的存儲空間小，SQL Server 載入索引結構的耗時少。由於索引結構是有序的，避免了全表掃描，也能提高查詢性能。使用窄的數據類型，使用數據壓縮，創建BTree索引，創建ClumnStore 索引，都能減少數據集占用的存儲空間，提高數據載入到記憶體的速度。SQL Server在執行用戶的查詢請求時，每一行數據都必須在記憶體中，因此，數據集占用的空間越少，載入的過程越快，SQL Server的查詢性能越高。

一，窄的數據行會節省存儲空間，減少IO次數

使用窄的數據類型，使行的寬度達到最小，在存儲相同數據量時，能夠節省存儲空間，減少Disk IO的次數。

在存儲日期數據時，Date占用3Byte，DateTime占用8Byte，DateTime2(2)占用6Byte，DateTime2(4)占用7Byte，DateTime2(7)占用8Byte。不管從表示的精度上，還是從占用的存儲空間上來看，DateTime2(N)都完勝DateTime。

例如，存儲‘yyyy-mm-dd MM:HH:SS’格式的日期數據，有以下4中選擇：

• 使用字元串 varchar(19) 或 nvarchar(19)存儲，十分不明智，前者占用19Byte後再占用38Byte；
• 使用數據類型 datetime2(7)存儲，占用8Byte，雖然精度更高，但是毫秒都是0，浪費存儲空間；
• 使用數據類型 datetime存儲，占用8Byte，如果需要存儲毫秒，datetime不滿足；
• 使用數據類型 datetime2(2)存儲，占用6Byte，相比較是最理想的。

由於SQL Server存儲數據是按照row存儲數據的，每個Page能夠存儲的數據行是有限的。在查詢同等數量的數據行時，如果row寬度窄，那麼每個page會容納更多的數據行，不僅減少IO次數，而且節省存儲空間。

二，在窄的數據列上創建index，能夠提高查詢性能

在窄的數據列上創建Index，索引結構占用的存儲空間更小，SQL Server消耗更少的Disk IO就能將索引結構載入到記憶體中，能夠提高查詢性能。

在創建Index時，必須慎重選擇聚集索引鍵，主要有兩個原因

1，聚集索引其實就是表本身，SQL Server必須保持物理存儲順序和邏輯存儲順序一致

在SQL Server中，Clustered Index能夠確定Table的物理存儲，使Table的物理存儲順序和聚集索引鍵的邏輯順序保持一致。在對Table數據進行update時，如果更新聚集索引鍵，導致數據行所在聚集索引鍵必須移動，此時，SQL Server不能“原地更新”數據行，必須將數據行移動到其應有的物理位置上，Table的物理存儲順序和聚集索引鍵的邏輯順序才能保持一致。SQL Server將Update命令拆分成等價的delete命令和insert 命令。

示例：聚集索引鍵4被修改為8，那麼，SQL Server將數據行5刪除，然後再相應的位置上插入數據行8。

如果插入的位置上沒有多餘的存儲空間，那麼，插入操作會導致頁拆分，產生索引碎片，影響查詢性能。

2，NonClustered Index的葉子節點中，都包含Clustered Index鍵。

例如，在表上有兩個索引：Clustered Index(c1,c2)，Nonclustered Index（c2,c3)，實際上，Nonclustered index的索引定義（c2,c3)include(c1)，即，在Nonclustered Index的葉子節點中，包含Clustered Index所有的Index Key。包含列和Index Key的區別在於，Index Key用於路由索引結構，而包含列用於返回數據，不提供搜索功能。

由於Clustered Index“無所不在”，Clustered Index的索引鍵最好創建在窄的，不變的，唯一的和只增長的數據列上。在創建Clustered Index時，最好是唯一索引（Unique Index）。窄的數據行會使每一個Index page存儲更多的index key，SQL Server Engine定位到某一行所經過的節點數更少，即導航的Path更短，載入和查詢速度更快。

由於每一個nonclustered index的Index pages或index key columns中都會包含Clustered Index key columns，如果Clustered Index key columns的寬度比較大，這會導致所有nonclustered index的索引樹占用較大的存儲空間，IO此次更多，更新和查詢都會變慢。

In general, it is best practice to create a clustered index on narrow, static, unique, and ever-increasing columns. This is for numerous reasons. First, using an updateable column as the clustering key can be expensive, as updates to the key value could require the data to be moved to another page. This can result in slower writes and updates, and you can expect higher levels of fragmentation. Secondly, the clustered key value is used in non-clustered indexes as a pointer back into the leaf level of the clustered index. This means that the overhead of a wide clustered key is incurred in every index created.

三，使用正確的數據類型，減少轉換的次數

在SQL Server中，對數據進行強制類型轉換或隱式類型轉換都需要付出代價，所以，使用正確的數據類型，避免類型轉換是十分必要的。例如，如果存儲的數據格式是‘yyyy-mm-dd MM:HH:SS’，雖然字元串類型和Datetime類型能夠隱式轉換，但是使用字元串類型 varchar(19)或 nvarchar(19)存儲是十分不明智的，不僅浪費存儲空間，而且隱式轉換對性能有負作用。

四，常見數據類型所占用的位元組數

數據類型大致分為四種：數值類型，日期和時間類型，字元串類型，GUID，使用DataLength()能夠查看任意數據類型的變數所占用的位元組數量

1，數值類型

對於整數類型，TinyInt 占用1Byte，表示的整數範圍是：0-255；SmallInt，int和bigint 分別占用2B，4B和8B。

對於小數類型，decimal(p,s)表示精確的小數類型，float(n)表示近似的小數類型，常用於表示百分比，除法的結果，有兩種類型float(24)占用4B，float(53)占用8B，參考《SQL Server的小數數值類型（float 和 decimal）用法》。

2，日期和時間類型

date表示日期，占用3B

Datetime2(n)，根據時間的毫秒部分來確定占用的位元組數量：當n是1，或2時，占用6B；當n是3，或4時，占用7B；當n是5，6，或7時，占用8B。

datetime占用8B，建議使用datetime2(n)來替代datetime。

3，字元類型

建議使用變長字元類型，varchar和nvarchar，後者占用的位元組是前者一倍；如果數據中都是拉丁字母，使用varchar更好。

4，UniqueIdentifier數據類型

UniqueIdentifier數據類型占用16B，GUID的格式是8-4-4-4-12，即'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'，不要使用varchar來存儲GUID。

例如，使用varchar來存儲GUID，將會浪費20B

declare @ui uniqueidentifier
declare @vc varchar(max)

set @ui=newid()
set @vc=cast(@ui as varchar(max))

select @ui,@vc,datalength(@ui),datalength(@vc)

如果表的數據行總量是5千萬，那麼使用varchar來存儲GUID將會浪費：953MB。在數據類型的選擇上，必須錙銖必較，能省就省。

五，示例

模擬一個場景：業務人員需要分析帖子，需要存儲的欄位：PostID，AuthorName，PostTitle，PostURL，PostContent，PostedTime。

在使用ETL同步社區爬蟲數據時，通常會額外增加兩個Column：DataCreatedTime和DataUpdatedTime，用於存儲新建數據行的時間和最後一次更新數據行的時間。

1，社區分析，通常涉及海量的數據，使用數據壓縮（data_compression=page），提高查詢性能。

2，增加代理鍵，使用代理鍵作為主鍵。

3，URL使用varchar類型，對於AuthorName，Title和Content需要使用unicode類型來存儲。

4，對於時間類型，精度不會很高，使用最節省的數據類型 datetime2(2)來存儲，錙銖必較。

5，將最占空間的PostContent和主表Posts分開，實際上是垂直分區，便於主表Posts的快速查詢。

create table dbo.Posts
(
PostID bigint identity(1,1) not null,
OriginalPostID bigint not null,
AuthorID int not null,
Title nvarchar(256) not null,
url varchar(2048) not null,
PostedTime datetime2(2) not null,
IsDeleted bit not null,
DataCreatedTime datetime2(2) not null,
DataUpdatedTime datetime2(2) not null,
constraint PK__Posts_ID primary key clustered(PostID)
)
with(data_compression=page);

create table dbo.Authors
(
AuthorID int Identity(1,1) not null,
OriginalAuthorID int not null,
Name nvarchar(128) not null,
DataCreatedTime datetime2(2) not null,
DataUpdatedTime datetime2(2) not null,
constraint PK__Authors_AuthorID primary key clustered(AuthorID)
)
with(data_compression=page);

create table dbo.PostContent
(
PostID int not null,
Content nvarchar(max) not null,
DataCreatedTime datetime2(2) not null,
DataUpdatedTime datetime2(2) not null,
constraint PK__PostContent_PostID primary key clustered(PostID)
)
with(data_compression=page);

推薦閱讀《Performance Considerations of Data Types》：
A clustered index created as part of a primary key will, by definition, be unique. However, a clustered index created with the following syntax,

CREATE CLUSTERED INDEX <index_name>
ON <schema>.<table_name> (<key columns>);

will not be unique unless unique is explicitly declared, i.e.

CREATE UNIQUE CLUSTERED INDEX <index_name>
ON <schema>.<table_name> (<key columns>);

In order for SQL Server to ensure it navigates to the appropriate record, for example when navigating the B-tree structure of a non-clustered index, SQL Server requires every row to have an internally unique id. In the case of unique clustered index, this unique row id is simply the clustered index key value. However, as SQL Server will not require a clustered index to be unique - that is, it will not prevent a clustered index
from accepting duplicate values - it will ensure uniqueness internally by adding a 4-byte uniquifier to any row with a duplicate key value.

In many cases, creating a non-unique clustered index on a unique or mostly unique column will have little-to-no impact. This is because the 4-byte overhead is only added to duplicate instances of an existing clustered key value. An example of this would be creating a non-unique clustered index on an identity column. However, creating a non-unique clustered index on a column with many duplicate values, perhaps on a column of date data type where you might have thousands of records with the same clustered key value, could result in a significant amount of internal overhead.
Moreover, SQL Server will store this 4-byte uniquifier as a variable-length column. This is significant in that a table with all fixed columns and a large number of duplicate clustered values will actually incur 8 bytes of overhead per row, because SQL Server requires 4 bytes to manage this variable column (2 bytes for the count of variable-length columns in the row and 2 bytes for the offset of the the variable-length column of the uniquifier column). If there are already variable-length columns in the row, the overhead is only 6 bytes—two for the offset and four for the uniquifier value. Also, this value will be present in all nonclustered indexes too, as it is part of the clustered index key.