Datetime 上的哪个 Where 条件会更快? [英] Which Where condition on Datetime will be faster?

问题描述

我在表中有一个名为 CreatedOn 的日期时间列.CreatedOn 也是非聚集索引的一部分，其中顺序是降序的.

I have a Datetime column called CreatedOn in a table. CreatedOn is also part of a non-clustered index where the order is descending.

以前在 where 条件中我有如下条件

Previously in the where condition I had a condition as follows

WHERE DateDiff(d,CreatedOn,GetDate()) < 180 AND ... other conditions

我把它改成

WHERE CreatedOn > '2012-04-04 00:00:00.000' AND ... other conditions

我在 C# 代码中计算截止日期，然后将其放入临时查询中.

where I am calculating the cutoff date in C# code and then putting that in the adhoc query.

据我所知，第二个条件应该更快，但我还没有看到查询执行时间的显着变化.但是随着表的大小增加，哪一个会运行得更快?

According to me, the second condition should be faster but I do not yet see a significant change in query execution times. But as the size of the table grows, which one will run faster?

推荐答案

第二种形式.

将函数放在列上会使索引的使用无效(在几乎所有情况下，因此只需始终遵循此规则).请参阅 十大常见 SQL 编程错误"，2号

Putting functions on columns invalidates use of indexes (in almost all cases, so simply follow this rule always). See "Ten Common SQL Programming Mistakes", number 2

这篇关于Datetime 上的哪个 Where 条件会更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！