英文:
Is the any advantage of putting CURRENT_TIMESTAMP into a sub select
问题
第一个查询是否比第二个查询性能更好？如果是的话，有没有比1=1更好的“加入时间戳”的方法？
第一个查询：
SELECT s.StudentId, count(*) as SchoolCount
FROM Student as s
JOIN (SELECT CURRENT_TIMESTAMP as CURR_DTM) AS DTM ON 1 = 1
JOIN StudentSchool ss
ON ss.StudentId = s.StudentId
AND (ss.StartDtm < CURR_DTM)
AND (ss.EndDtm IS NULL OR ss.EndDtm > CURR_DTM)
GROUP BY s.StudentId
第二个查询：
SELECT s.StudentId, count(*) as SchoolCount
FROM Student as s
JOIN StudentSchool ss
ON ss.StudentId = s.StudentId
AND (ss.StartDtm < CURRENT_TIMESTAMP)
AND (ss.EndDtm IS NULL OR ss.EndDtm > CURRENT_TIMESTAMP)
GROUP BY s.StudentId
在SSMS中，不确定如何分析这两个查询。它们对我来说都给出了相同的结果。
英文:
Would the first query be more performant than the second one?
And if so is there a better way to "join in the timestamp" than 1=1
SELECT s.StudentId, count(*) as SchoolCount
FROM Student as s
JOIN (SELECT CURRENT_TIMESTAMP as CURR_DTM) AS DTM ON 1 = 1
JOIN StudentSchool ss
ON ss.StudentId = s.StudentId
AND (ss.StartDtm < CURR_DTM)
AND (ss.EndDtm IS NULL OR ss.EndDtm > CURR_DTM)
GROUP BY s.StudentId
SELECT s.StudentId, count(*) as SchoolCount
FROM Student as s
JOIN StudentSchool ss
ON ss.StudentId = s.StudentId
AND (ss.StartDtm < CURRENT_TIMESTAMP)
AND (ss.EndDtm IS NULL OR ss.EndDtm > CURRENT_TIMESTAMP)
GROUP BY s.StudentId
No sure how to analyze this in SSMS. Gave me the same results for both.
答案1
得分: 0
当在查询中多次使用确定性函数时，它仅执行一次，并与一个变量关联，该变量替代了代码中出现的函数调用。
rand()、CURRENT_TIMESTAMP、GETDATE()、SYSDATETIME() 等函数本身具有相同的行为。
值得注意的例外：NEWID()
查询的编写者似乎认为两次调用CURRENT_TIMESTAMP函数的值可能不同，这是错误的...
英文:
when a deterministic function is used several times in a query it is executed only once and associated with a variable which replaces the call of the function everywhere in the code where it is present.
The rand(), CURRENT_TIMESTAMP, GETDATE(), SYSDATETIME()... functions themselves have the same behavior.
Notable exception: NEWID()
The writer of the query seems to think that the value of the two calls of the function CURRENT_TIMESTAMP could have different values which is false...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论