淺析SQL Server datetime數據類型設計與優化誤區
一、場景
在SQL Server 2005中，有一個表TestDatetime，其中Dates這個字段的數據類型是datetime，如果你看到表的記錄如下圖所示，你***想到的是什么呢？
（圖1：數據列表）
你看到這些數據，是不是覺得這樣的設計既浪費了存儲空間，又使得這個列的索引增大，查詢起來更慢，你也想使用一些其它的數據類型來代替這個datetime吧？
其實大家都是這么想的，這個方向是100%正確的，但是在寫這篇文章以前，我進入了兩個誤區：（如果你中了下面的兩個誤區，那么請你看看這篇文章吧。）
誤區一： 把Dates字段的datetime數據類型換成smalldatetime，這樣數據就由：‘2009-04-09 00:00:00.000’變為‘2009-04-09 00:00:00’，這個看起來沒有減少多少存儲空間哦。
誤區二：把Dates字段的datetime數據類型換成char(10)，這樣數據就由：‘2009-04-09 00:00:00.000’變為‘2009-04-09’，這好像能減少很多存儲空間哦。
二、分析
在SQL Server 2005版本中保存日期的數據類型只有兩種：datetime、smalldatetime，但是在SQL Server 2008版本中新增了一些日期數據類型：time、date、smalldatetime、datetime、datetime2、datetimeoffset，其中的date類型就能滿足我們場景中的需求了，如果你幸運的在使用SQL Server 2008的話，那么恭喜你，請使用date數據類型吧。
但是我就比較可悲一點了，在使用SQL Server 2005的前提下，我進入了誤區一、誤區二。其實這也是因為自己忽略了一下基礎性的東西，如果知道不同數據類型的存儲空間大小，也許就很輕易的避免這樣低級的錯誤了。
其實你查看表TestDatetime中的Dates字段的時候，看到查詢結果中的：“-”、“:”只是用于顯示的，并不是真實保存的時候就這樣格式的。
datetime占用8個字節，前4個字節存儲base date（即1900年1月1日）之前或之后的天數，后4個字節存儲午夜后的毫秒數。值范圍：1753-01-01 到 9999-12-31。
smalldatetime占用4個字節，前2個字節存儲base date（1900年1月1日）之后的天數。后2個字節存儲午夜后的分鐘數。值范圍：1900-01-01 到 2079-06-06。
date占用3個字節，它比smalldatetime的前2個字節多了1字節，所以值的范圍更廣了。值范圍：0001-01-01 到 9999-12-31。
所以，如果你使用char(10)來保存截斷的日期，那么你的存儲空間反而更大了。
結論： 如果是SQL Server 2005，那么請你使用smalldatetime吧，數據能節約一半，雖然查詢的時候看起來沒什么改變；如果你是SQL Server 2008，那么請你使用date吧，
雖然3個字節跟4個字節沒有多大的差距，但是從設計上和邏輯清晰度上都有很大的提升，而且差距有些時候并不是1個字節的問題，比如當表數據量達到幾個億的時候，還是有差別的，又或者一條記錄可能因為差1個字節就剛剛好給8060字節的頁瓜分，這些都不容忽視的。
三、測試
下面我們就從數據存儲的大小、索引存儲的大小、索引使用時候的速度這幾個方面進行測試：（這里只測試數據類型：，，數據的內容都是一樣的）
(一) 測試前奏：
1. 創建三種數據類型char(10)、datetime、smalldatetime的表；（表結構如下面SQL）
- CREATE TABLE [dbo].[TestDatetime](
- [Id] [int] IDENTITY(1,1) NOT NULL,
- [Dates] [datetime] NULL,
- CONSTRAINT [PK_TestDatetime] PRIMARY KEY CLUSTERED
- (
- [Id] ASC
- )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
- ) ON [PRIMARY]
2. 插入相同記錄到三個表中；（這里插入1210000條記錄）
3. 為[Dates]字段創建索引；（在創建索引的時候可以設置填充因子為100%）
4. 查看索引屬性中的索引碎片信息，查看表數據和索引占用的空間，測試[Dates]字段索引的查詢效率；
(二) 測試結果：
1. 數據存儲大小：
（圖2：數據空間對比）
2. 索引存儲信息：
（圖3：char(10)）
（圖4：datetime）
（圖5：smalldatetime）
3. 索引查詢的情況：
多次執行，SQL Server執行時間為：[char(10)] 大部分在43~59徘徊，偶爾出現小于10的；[datetime]平均在1~2毫秒；[smalldatetime]均在1毫秒；而且大家會發現[smalldatetime]有其它的9次邏輯讀取變為8次了。
--[TestChar10]
SQL Server 分析和編譯時間:
CPU 時間= 0 毫秒，占用時間= 1 毫秒。
(2200 行受影響)
表'TestChar10'。掃描計數1，邏輯讀取9 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。
SQL Server 執行時間:
CPU 時間= 0 毫秒，占用時間= 59 毫秒。
SQL Server 執行時間:
CPU 時間= 0 毫秒，占用時間= 1 毫秒。
--[TestDatetime]
SQL Server 分析和編譯時間:
CPU 時間= 0 毫秒，占用時間= 1 毫秒。
(2200 行受影響)
表'TestDatetime'。掃描計數1，邏輯讀取9 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。
SQL Server 執行時間:
CPU 時間= 0 毫秒，占用時間= 2 毫秒。
SQL Server 執行時間:
CPU 時間= 0 毫秒，占用時間= 1 毫秒。
--[TestSmalldatetime]
SQL Server 分析和編譯時間:
CPU 時間= 0 毫秒，占用時間= 1 毫秒。
(2200 行受影響)
表'TestSmalldatetime'。掃描計數1，邏輯讀取8 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。
SQL Server 執行時間:
CPU 時間= 0 毫秒，占用時間= 1 毫秒。
SQL Server 執行時間:
CPU 時間= 0 毫秒，占用時間= 1 毫秒。
--SQL Server 2008新數據類型
- SELECT
- CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'
- ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'
- ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS
- 'smalldatetime'
- ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'
- ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2'
- ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset';
四、參考文獻
日期和時間數據類型及函數 (Transact-SQL)
LEN (Transact-SQL)
DATALENGTH (Transact-SQL)
smalldatetime和datetime存儲
原文鏈接：http://www.cnblogs.com/gaizai/archive/2010/11/26/1888505.html
【編輯推薦】
