SQL Server 2008 datetime类型时间比较
以下查询是 SQL Server 2008 中仅按时间部分日期时间字段进行筛选的最低效方法吗？
SELECT *
FROM mytable
WHERE CAST([DATEEND] as TIME ) > "5:00PM";
在 where 语句中进行类型转换时是否存在任何重大警告或性能问题？
有没有更好的替代方案或最佳实践？
Is the following query the most inefficient way to filter by only the time part datetime field in SQL Server 2008?
SELECT *
FROM mytable
WHERE CAST([DATEEND] as TIME ) > "5:00PM";
Are there any major caveats or performance issues in doing the type casting in the where statement?
Are there any better alternatives or best practices?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
此查询的主要问题是它不可控制。即使
DATEEND列已建立索引，也无法使用该索引。使此查询索引友好的唯一方法是使用定义为
CAST([DATEEND] as TIME)的计算列，然后为其建立索引。即使您要这样做，您也可能会发现索引未被使用，因为它将取决于查询的选择性。当您的查询使用
*时，索引将需要进行键查找以检索未覆盖的列。使用索引的确切选择性取决于“临界点”您还可以通过
INCLUDE来覆盖缺失的列，但是当您选择表中的所有列时，这将使索引非常宽，这意味着需要更多的逻辑读取来扫描它还有维护数据修改语句的成本更高。The main issue with this query is that it is unsargable. Even if the
DATEENDcolumn is indexed then the index cannot be used.The only way of making this query index friendly would be to have an computed column with definition
CAST([DATEEND] as TIME)and then index it.Even if you were to do that you may well find that the index isn't used though as it will depend how selective the query is. As your query uses
*the index will need to do key lookups to retrieve the non covered columns. The exact selectivity at which the index would be used depends on the "Tipping Point"You could also make the index covering by
INCLUDE-ing the missing columns but as you are selecting all columns in the table that will make the index very wide which means more logical reads to scan it as well as more expensive to maintain for data modification statements.