问题描述
我有一个触发器，可以在输入值时自动将给定条目的 CreationDate 和 ModifiedDate 设置为当前的 UTC 时间.(此后CreationDate 将保持不变，而ModifiedDate 将在每次更新时通过另一个触发器进行更新).
I have a trigger that automatically sets the CreationDate and ModifiedDate of the given entry to the current UTC time whenever a value is entered. (CreationDate will remain the same thereafter, while ModifiedDate will be updated on each update via another trigger).
我想确保已插入且永不更新的项目具有与 CreationDate 和 ModifiedDate 完全相同的值，因此我使用了如下变量:
I want to make sure that inserted-and-never-updated items will have exactly the same value for CreationDate and ModifiedDate, so I've used a variable like this:
DECLARE @currentTime DATETIME
SELECT @currentTime = GETUTCDATE()
UPDATE dbo.MyTable SET CreationDate = @currentTime, ModifiedDate = @currentTime
...
在我的命令式编程心态中，我假设这可以防止 GETUTCDATE() 被调用两次，并可能产生略有不同的结果.这真的有必要吗?如果不是，这会更贵，更便宜，还是和下面的代码完全一样?
In my imperative-programming mentality, I am assuming this prevents GETUTCDATE() from being called twice, and potentially producing slightly different results. Is this actually necessary? If not, would this be more expensive, less expensive, or exactly the same as the following code?
UPDATE dbo.MyTable SET CreationDate = GETUTCDATE(), ModifiedDate = GETUTCDATE()
...
推荐答案
DECLARE @Counter INT = 1
WHILE (1 = (SELECT 1 WHERE GETUTCDATE() = GETUTCDATE()))
SET @Counter = @Counter+1
SELECT @Counter /*Returns almost immediately with a number in the 000s for me.*/
并且只是为了证明在 SELECT 列表中也会发生这种情况.
And just to prove this happens when in the SELECT list also.
DECLARE @T TABLE
(
rownum INT IDENTITY(1,1) PRIMARY KEY,
d1 datetime,
d2 datetime
)
WHILE (NOT EXISTS(SELECT * FROM @T WHERE d1 <> d2))
BEGIN
DELETE FROM @T
INSERT INTO @T
SELECT GETUTCDATE(),GETUTCDATE()
END
SELECT * FROM @T
顺便说一句:如果出于某种原因您想在每行的基础上评估 GETUTCDATE()，您可以将其包装在标量 UDF 中.
BTW: IF for some reason you want to evaluate GETUTCDATE() on a per row basis you can wrap it in a scalar UDF.
CREATE FUNCTION dbo.GETUTCDATE()
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
RETURN GETUTCDATE()
END
GO
SELECT GETUTCDATE(),dbo.GETUTCDATE()
FROM master..spt_values
这篇关于如果在同一语句中使用两次，GETUTCDATE() 会返回相同的值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！