SQL Getdate的精度? [英] Precision of SQL Getdate?

问题描述

我正在试验一个程序，该程序可以将数据高速插入SQL 2005 Server数据库(在XP SP3上). (这是为了收集时序数据，以便我可以评估设计的不同方面.)

我的基本设置包括将数据插入到如下表中(并使用仅指定有效负载字段的SP):

create table data
(
 Id int  PRIMARY KEY Identity,
 payload datatime not null,
 inserted datetime default (getdate()) not null
)

请注意，两个日期时间字段也都具有唯一性约束.

在客户端程序上，我在一个紧密的循环中调用SP，以至于.Net DateTime.Now值(可能还有线程休眠)的精度出现问题，从而违反了有效负载的唯一约束.我通过组合秒表变量，一点Thread.Sleep()和手动构造有效载荷"数据来解决问题，以使其不违反SQL DateTime字段的分辨率(3.3 mS)

但是，以5mS到10mS之间的速率生成插入时，我开始发现SQL端的"Inserted"字段存在问题，在该字段中，由于唯一键冲突而经常拒绝某行.只有当我将插入速率降低到15毫秒左右时，此问题才消失.这个速率令人怀疑，就像我对.Net DateTime.Now遇到的精度问题一样(我在某处的某处看到16mS的读数)，因此我想知道SQL Getdate()函数的实际精度是多少.

那么有人可以告诉我什么支持GetDate()，它将与.Net DateTime.Now值绑定到相同的源吗?我应该从中获得什么样的精度?

顺便说一句，我知道SQL 2008 Server中的DATETIME2类型，因此引发了一个问题，即该系统中GetDate()的精度也是如此.

解决方案

DATETIME的精度为3.3ms，但是您发现的GETDATE()不会返回准确的时间.有关详细的日期/时间类型/功能，请查看 MSDN页面有关新类型/功能如何工作的信息.

I am experimenting with a program that inserts data into an SQL 2005 Server database (on XP SP3) at high rate of speed. (This is for collecting timing data so I can evaluate different aspects of my design).

My basic set up involves inserting a data into a table like the following (and using an SP that just specifies the payload field):

create table data
(
 Id int  PRIMARY KEY Identity,
 payload datatime not null,
 inserted datetime default (getdate()) not null
)

Note that both datetime fields have UNIQUE constraints on them as well.

On the client program I was calling the SP in such a tight loop that I had problems with the precision of the .Net DateTime.Now value (and possibly thread sleeping as well) and hence violating the payload's unique constraint. I addressed by a combination of a stopwatch variable, a bit of Thread.Sleep() and manually constructing the "payload" data so that it didn't violate the resolution of the SQL DateTime field (3.3 mS)

However with the inserts being generated at a rate between 5mS and 10mS I have started to see issues with the "Inserted" field on the SQL side where a row is being rejected regularly for a unique key violation. It is only when I slow my insert rate to more than 15 or so mS that this problems goes away. This rate is suspiciously like the precision issue I had with the .Net DateTime.Now (I read 16mS on a post somewhere) so I am wondering what the actual precision of the SQL Getdate() function is.

So can someone tell me what is backing GetDate(), and would it be tied to the same source as the .Net DateTime.Now value? And what sort of precision should I expect from it?

As an aside I know about the DATETIME2 type in SQL 2008 server, so that raises the question as to what the precision is for GetDate() in that system as well.

解决方案

DATETIME has a precision of 3.3ms, but GETDATE() does not return times accurate to this as you've discovered. Check out the MSDN page for date/time types/functions for more info on how the new types / functions work.

这篇关于SQL Getdate的精度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！