一、场景
在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
分享到:
相关推荐
如何对SQL Server datetime类型数据优化.pdf
SqlServer中使用Convert取得DateTime数据格式 标签: Sqlserver数据库技术
sql server中使用convert来取得datetime数据类型样式(全) 日期数据格式的处理,两个示例: CONVERT(varchar(16), 时间一, 20) 结果:2007-02-01 08:02/*时间一般为getdate()函数或数据表里的字段*/ CONVERT...
如果你是SQLServer2008,那么请你使用date吧在SQLServer2005中,有一个表TestDatetime,其中Dates这个字段的数据类型是datetime,如果你看到表的记录如下图所示,你最先想到的是什么呢?(图1:数据列表)你看到这些...
SQL的datetime类型数据转换为字符串格式大全 2007-10-10 11:11:43 2007/10/10 SELECT convert(char(20),getdate(),120) as TIME_1,convert(char(20),getdate(),111) as TIME_2 将字符串20100426103059转换为...
本书涵盖了T-SQL程序设计的方方面面,如基于集合的编程技术、日期和时间相关的XML和CLR数据类型的使用、临时对象、T-SQL和CLR用户自定义函数、存储过程、触发器、事务和新的错误处理结构、应用并发模型支持并发用户...
二、如何在sql语句中判断为NULL的时间字段 假设表为:TestTable SN DateTime1 DateTime2 1 2011-10-24 2011-10-25 2 NULL 2011-10-26 3 2011-10-25 NULL 用case进行查询,若写成: select (case DateTime1 ...
C#往sqlserver数据库中插入时间型数据时,直接插入null,数据库中会变成1900-1-1的数据,这个demo使用了参数的方法进行插入,很简单
SQL SERVER数据库用datetime类型的时候 需要注意的地方
1.SQL SERVER的数据类型 数据类弄是数据的一种属性,表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然,不同的程序语言都具有不同的特点,所定义的数据类型的各类和名称都或多或少有些...
可将sql server 数据表生成sql语句 语句中包括建表部分和数据部分 常用作sql server 数据导入 例如:--表 buyi_product_comment 的结构 CREATE TABLE buyi_product_comment(proId varchar(50) not null,companyid ...
简单的一个WORD文档,将DateTime的数据类型和应用全部列出来了,收了就能看
7、小结 8、思考与练习 第2章 SQL Server 数据类型 一、教学目标: 1.掌握各种数据类型的特点和基本用法 2.学会创建和删除用户自定义数据类型 二、教学重点和难点: 教学重点:掌握各种数据类型的用法 教学难点:...
SQL的datetime类型数据转换为字符串格式大全.doc
与 Microsoft Visual Studio 2005 集成之后,SQL Server Express 可以轻松的开发功能强大且安全的数据驱动应用程序,并允许快速部署这些应用程序。SQL Server Express 可以根据协议重新进行发布,并可作为客户端...
SqlServer数据类型“datetime”存在准确性和舍入问题,并且已被没有此类问题的“datatime2”数据类型取代。但是,“datetime”仍然存在于许多旧数据库中。我们将展示.NET Entity Framework环境中的这些问题如何导致...
中国省市区街道 sql(SQL server) INSERT [dbo].[Whir_Cmn_Area] ([Id], [Name], [EnName], [Pid], [ParentPath], [State], [IsDel], [Sort], [CreateDate], [CreateUser], [UpdateDate], [UpdateUser], [Code]) ...
此文档中详细记载了,SQL Server使用convert对datetime日期数据进行获取,希望可以帮到下载的朋友们!