SQL Server:对 GETDATE() 感兴趣 [英] SQL Server: intrigued by GETDATE()
问题描述
我有 SQL Server 2005 Standard Service Pack 2 9.00.4053.00 (Intel X86)
I have SQL Server 2005 Standard Service Pack 2 9.00.4053.00 (Intel X86)
表有近 3000 万行..
Table has close to 30 million rows..
如果我这样做
SELECT GETDATE(), * FROM
<table>
返回相同的日期和时间值，包括毫秒部分..虽然查询需要超过 3 分钟才能完成...
Identical Date and time value is returned including milliseconds part.. though query took more then 3 minutes to complete...
我已经阅读了
http://sqlblog.com/blogs/andrew_kelly/archive/2008/02/27/when-getdate-is-not-a-constant.aspx
我发布的一个链接(标记答案)表明在 SQL 2005 GETDATE 之前是确定性的尽管 SQL 2000 BOL 声明 GETDATE 是不确定的
One of the link I posted (marked answer) suggest that prior to SQL 2005 GETDATE was deterministic although SQL 2000 BOL states GETDATE is nondeterministic
如果我对数百万行进行更新
If I do an update with millions of rows
UPDATE tableName
SET dateColumn = GETDATE()
我知道你很想做
DECLARE @DT datetime
SET @DT = GETDATE()
UPDATE table
SET datecol =@DT
我真的很困惑
预期的行为是什么?
- 对于我之前发布的 select 语句
- 更新语句的行为
考虑到您正在更新具有 1 亿行的表上的 datecolundatecolumn 是否会以毫秒为单位具有相同的日期和时间......?
Considering you are updateing a datecolun on a table with 100 million rows Would datecolumn will have identical date and time in milliseconds....?
推荐答案
继 Martin Smith 的回答之后，所指的决定论是 udf 行为的改变.在 SQL Server 2000 中，不能在 udf 中使用 GETDATE.您可以在 SQL Server 2005 中. 也请参阅此链接
Following on from Martin Smith's answer, The determinism referred to was a change in udf behaviour. In SQL Server 2000, you could not use GETDATE in a udf. You can in SQL Server 2005. See this link too
正如 Martin Smith 所说，有些函数是按列、按查询计算的.不是每行.GETDATE 是一个，RAND 是另一个.
As Martin Smith said, some functions are evaluated per column, per query. Not per row. GETDATE is one, RAND is another.
如果您确实需要对 GETDATE 进行逐行评估，请将其包装在 udf 中.
If you do need row by row evaluation of GETDATE then wrap it in udf.
NEWID 在统计上是唯一的.它必须逐行评估，这样您就不会在另一行中出现相同的值.因此 CHECKSUM(NEWID()) 技巧来生成逐行随机数...
NEWID is statistically unique. It must be evaluated row by row so you don't have the same value appear in another row. Hence the CHECKSUM(NEWID()) trick to generate row by row random numbers...
这篇关于SQL Server:对 GETDATE() 感兴趣的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
