SQLServerdatetime数据类型设计以及优化误区

导读：SQL Server数据库的发展就离不开数据库的数据类型设计以及数据库的优化，要想做好数据库中数据类型设计以及数据库优化工作，就首先要熟练掌握下文中介绍的数据类型设计以及数据库优化的误区。

十年的青羊网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。营销型网站的优势是能够根据用户设备显示端的尺寸不同，自动调整青羊建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联从事“青羊网站设计”,“青羊网站推广”以来，每个客户项目都认真落实执行。

场景：在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：数据空间对比）

索引存储信息：

（图3：char(10)）

（图4：datetime）

（图5：smalldatetime）

索引查询的情况：

多次执行，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';

SQL Server datetime数据类型设计以及数据库优化误区就为大家总结这么多，可能还不够全面，以后有机会还会继续为大家介绍更多的总结，希望大家都能够从中有所收获。

【编辑推荐】

1. SQL Server 2005 自动化删除表分区设计方案
2. SQL Server数据库中对图片进行保存和输出
3. 使用SQL Server 2008导入平面文件
4. SQL Server 2008内存性能监控

本文题目：SQLServerdatetime数据类型设计以及优化误区
分享URL：http://www.gawzjz.com/qtweb/news35/196135.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联