sql server设计与优化中DATETIME的一些误区.docVIP

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’，这好像能减少很多存储空间哦。 !--[if !supportLists]--二、!--[endif]--分析 在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字节的页瓜分，这些都不容忽视的。 !--[if !supportLists]--三、!--[endif]--测试 下面我们就从数据存储的大小、索引存储的大小、索引使用时候的速度这几个方面进行测试：（这里只测试数据类型：，，数据的内容都是一样的） !--[if !supportLists]--(一) !--[endif]--测试前奏： !--[if !supportLists]--1. !--[endif]--创建三种数据类型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] !--[if !supportLists]--2. !--[