SQL Getdate的精度？

发布于 2024-09-12 06:52:21 字数 862 浏览 3 评论 0原文

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

我的基本设置包括将数据插入到如下所示的表中（并使用仅指定有效负载字段的 SP）：

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

请注意，两个日期时间字段也都具有 UNIQUE 约束。

在客户端程序上，我在如此紧密的循环中调用 SP，以至于我遇到了 .Net DateTime.Now 值的精度问题（也可能是线程休眠），因此违反了有效负载的唯一约束。我通过结合秒表变量、一些 Thread.Sleep() 和手动构建“有效负载”数据来解决这个问题，这样它就不会违反 SQL DateTime 字段的分辨率（3.3 mS），

但是随着插入的生成以 5 毫秒到 10 毫秒之间的速度，我开始看到 SQL 端“插入”字段的问题，其中一行因唯一键违规而被定期拒绝。只有当我将插入速率降低到 15 毫秒左右时，这个问题才会消失。这个速率可疑地类似于我在 .Net DateTime.Now 中遇到的精度问题（我在某处的帖子上读到 16mS），所以我想知道 SQL Getdate() 函数的实际精度是多少。

那么有人可以告诉我什么支持 GetDate()，它是否与 .Net DateTime.Now 值绑定到相同的源？我应该期望它达到什么样的精度？

顺便说一句，我了解 SQL 2008 服务器中的 DATETIME2 类型，因此这就提出了该系统中 GetDate() 的精度是多少的问题。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一袭白衣梦中忆 2024-09-19 06:52:21

DATETIME 的精度为 3.3ms，但正如您所发现的，GETDATE() 不会返回精确于此的时间。有关详细信息，请查看 MSDN 页面了解日期/时间类型/函数有关新类型/功能如何工作的信息。

回复收藏 0 原文

孤者何惧 2024-09-19 06:52:21

DATETIME 存储为 2 个整数。一个代表日期部分，另一个代表时间部分（午夜后的刻度数），每个刻度是 1/300 秒，因此它的理论精度至少为 3.3 毫秒。

我只是尝试在我的机器上运行它

declare @d  varchar(24)

while 1=1 
begin
set @d=CONVERT(VARCHAR(24), GETDATE(), 113)
raiserror('%s',0,1, @d) with nowait
end

，并且运行了相当长的时间，它确实一次上升一个刻度，所以我认为不会有任何固有的限制阻止它实现这一点。

01 Aug 2010 00:56:53:913
...
01 Aug 2010 00:56:53:913
01 Aug 2010 00:56:53:917
...
01 Aug 2010 00:56:53:917
01 Aug 2010 00:56:53:920
...
01 Aug 2010 00:56:53:920
01 Aug 2010 00:56:53:923

关于 SQL Server 2008 中有关 GetDate() 精度的查询，这与 SQL2005 中的查询相同。 sysdatetime 旨在具有更高的精度。我刚刚尝试运行以下命令，并对两个结果之间的差异感到惊讶。

SET NOCOUNT ON

CREATE TABLE #DT2(
[D1] [datetime2](7) DEFAULT (getdate()),    
[D2] [datetime2](7) DEFAULT (sysdatetime())
) 
GO

INSERT INTO #DT2
          DEFAULT  VALUES
GO 100

SELECT DISTINCT [D1],[D2],DATEDIFF(MICROSECOND, [D1], [D2]) AS MS
 FROM #DT2

结果

D1                           D2                              MS
----------------------------    -----------------------      ------
2010-08-01 18:45:26.0570000   2010-08-01 18:45:26.0625000     5500
2010-08-01 18:45:26.0600000   2010-08-01 18:45:26.0625000     2500
2010-08-01 18:45:26.0630000   2010-08-01 18:45:26.0625000     -500
2010-08-01 18:45:26.0630000   2010-08-01 18:45:26.0781250     15125
2010-08-01 18:45:26.0670000   2010-08-01 18:45:26.0781250     11125
2010-08-01 18:45:26.0700000   2010-08-01 18:45:26.0781250     8125

DATETIME is stored as 2 integers. One representing the date part and the other the time part (number of ticks after midnight) each tick is 1/300 of a second so it has at least a theoretical precision of 3.3 milliseconds.

I just tried running this on my machine

declare @d  varchar(24)

while 1=1 
begin
set @d=CONVERT(VARCHAR(24), GETDATE(), 113)
raiserror('%s',0,1, @d) with nowait
end

And got a fairly lengthy run where it did go up one tick at a time so I don't think there can be any inherent limitation that prevents it achieving that.

01 Aug 2010 00:56:53:913
...
01 Aug 2010 00:56:53:913
01 Aug 2010 00:56:53:917
...
01 Aug 2010 00:56:53:917
01 Aug 2010 00:56:53:920
...
01 Aug 2010 00:56:53:920
01 Aug 2010 00:56:53:923

Regarding your query about GetDate() precision in SQL Server 2008 this is the same as for SQL2005. sysdatetime is meant to have higher precision. I just tried running the following and was surprised by the discrepancy between the two results.

SET NOCOUNT ON

CREATE TABLE #DT2(
[D1] [datetime2](7) DEFAULT (getdate()),    
[D2] [datetime2](7) DEFAULT (sysdatetime())
) 
GO

INSERT INTO #DT2
          DEFAULT  VALUES
GO 100

SELECT DISTINCT [D1],[D2],DATEDIFF(MICROSECOND, [D1], [D2]) AS MS
 FROM #DT2

Results

D1                           D2                              MS
----------------------------    -----------------------      ------
2010-08-01 18:45:26.0570000   2010-08-01 18:45:26.0625000     5500
2010-08-01 18:45:26.0600000   2010-08-01 18:45:26.0625000     2500
2010-08-01 18:45:26.0630000   2010-08-01 18:45:26.0625000     -500
2010-08-01 18:45:26.0630000   2010-08-01 18:45:26.0781250     15125
2010-08-01 18:45:26.0670000   2010-08-01 18:45:26.0781250     11125
2010-08-01 18:45:26.0700000   2010-08-01 18:45:26.0781250     8125

回复收藏 0 原文

~没有更多了~