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新資料類型

   1. SELECT

   2. CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'

   3. ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'

   4. ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS

   5. 'smalldatetime'

   6. ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'

   7. ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2'

   8. ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetime

原文連結：http://www.365master.com/manage/3/2010/1129/60900.shtml