淺析SQL Server datetime資料型別設計與最佳化誤區
今天我們將探討SQL Server datetime資料型別設計與最佳化誤區,希望這些基礎性的知識能對大家有所幫助。
原文連結:http://www.cnblogs.com/gaizai/archive/2010/11/26/1888505.html
[@more@]一、場景
在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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/66009/viewspace-1058302/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Server中datetimeset轉換datetime型別問題淺析SQLServer型別
- 淺析SQL Server 2008綜合資料可程式設計性SQLServer程式設計
- SQL Server-資料型別SQLServer資料型別
- SQL Server資料型別BLOBSQLServer資料型別
- Oracle基本資料型別儲存格式淺析——RAW型別Oracle資料型別
- Oracle基本資料型別儲存格式淺析(五)——RAW型別Oracle資料型別
- Sql Server之資料型別詳解SQLServer資料型別
- SQL Server 中自定義資料型別SQLServer資料型別
- SQL Server中操作XML型別資料SQLServerXML型別
- SQL SERVER與C#的資料型別對應表SQLServerC#資料型別
- DAMA認證|一文淺析資料治理與資料管理的區別
- Oracle基本資料型別儲存格式淺析(四)——ROWID型別Oracle資料型別
- Oracle基本資料型別儲存格式淺析(三)——日期型別(一)Oracle資料型別
- Oracle基本資料型別儲存格式淺析(三)——日期型別(二)Oracle資料型別
- Oracle基本資料型別儲存格式淺析(三)——日期型別(三)Oracle資料型別
- Oracle基本資料型別儲存格式淺析(三)——日期型別(四)Oracle資料型別
- sqlite資料型別 datetime處理SQLite資料型別
- Oracle基本資料型別儲存格式淺析[zt]Oracle資料型別
- sql server agent與sql server有什麼區別 ?SQLServer
- SQL Server日期資料型別DATE的使用SQLServer資料型別
- sql server 資料型別轉換函式SQLServer資料型別函式
- Sql Server基礎:資料型別詳解SQLServer資料型別
- MySql和SQL Server資料型別 對比MySqlServer資料型別
- SQL Server 中的25種資料型別SQLServer資料型別
- SQL Server中易混淆的資料型別SQLServer資料型別
- Sql Server 資料型別 轉換 函式SQLServer資料型別函式
- Sql Server資料型別轉換函式SQLServer資料型別函式
- Oracle Xmltype型別淺析OracleXML型別
- Timestamp型別淺析型別
- SQL與NoSQL(關係型與非關係型)資料庫的區別SQL資料庫
- Oracle基本資料型別儲存格式淺析(二)——數字型別Oracle資料型別
- 淺析SQL Server效能分析引數SQLServer
- SQL Server時間格式淺析 (轉)SQLServer
- sql學習過程1:sql server資料型別解析SQLServer資料型別
- 讀TiDB原始碼聊設計:淺析HTAP的SQL最佳化器TiDB原始碼SQL
- sql server使用convert來取得datetime日期資料SQLServer
- Sql Server系列:資料型別轉換函式SQLServer資料型別函式
- SQL Server 中易混淆的資料型別(轉)SQLServer資料型別