SQL Server 2008 R2 中奇怪的 CAST() 行为 [英] strange CAST() behaviour in SQL Server 2008 R2

问题描述

我在数据库中有一张表.表由两列组成.DeliveryDate 的第一列它的类型为 DATETIMEDeliveryTime 的第二列它的类型为 VARCHAR

I have got a table in DataBase. Table consists of two columns. First column for DeliveryDate it has a type of DATETIME Second column for DeliveryTime it has a type of VARCHAR

这是一个表格数据的小例子

Here is a little example of table data

          DeliveryDate       -  DeliveryTime  
    2014-11-06 00:00:00.000  -     15:00
    2014-11-06 00:00:00.000  -     15:00
    2014-11-12 00:00:00.000  -     09:00
    2014-11-12 00:00:00.000  -     09:00

表中有 92 行.

目标是进行查询，将 DeliveryDate 和 DeliveryTime 连接到单个 DATETIME 列

The goal is to make a query that concats DeliveryDate and DeliveryTime to a single DATETIME column

我提出这样的要求

SELECT CAST((O.DeliveryDate + RIGHT(ISNULL(O.DeliveryTime,'00:00'),5)) AS DATETIME)
FROM MySuperTable

查询失败，错误是从 varchar 转换无效

Query fails with error of invalid conversion from varchar

但是如果我使用 TOP 进行查询，例如 TOP 92.

But if i make query with TOP for example TOP 92.

SELECT TOP 92 CAST((O.DeliveryDate + RIGHT(ISNULL(O.DeliveryTime,'00:00'),5)) AS DATETIME)
FROM MySuperTable

查询完成且没有错误.而且 TOP 直到 620 是 ok nut TOP 621 再次失败.请帮助理解顶级逻辑的工作原理以及我的代码有什么问题

Query completes without errors. Moreover TOP until 620 is ok nut TOP 621 again fails. Please help to undestand how top logic works and whats the problem with my code

推荐答案

这是由于查询优化器及其优化查询的方式.它很可能认为它可以重新安排一两个步骤以加快速度，而没有意识到数据问题的存在.这就是为什么

This is due to the Query Optimizer and how it optimizes the query. It most likely assumed it could rearrange a step or two to make things faster, not realizing that the data issue existed. This is why

• 在只有 92 行时执行 TOP 620 工作，因为它可能会影响优化器构建获取数据路径的方式
• 将计算与 UDF 分开是可行的，因为它阻止优化器采用导致问题的任何捷径.

• doing the TOP 620 worked when there were only 92 rows as it was likely affecting the way the optimizer built the path on how to get the data
• separating the calculation to the UDF worked as it stopped the optimizer from being able to take whatever shortcut was causing the issue.

但是 UDF 非常慢.相反，尝试在您的查询中更加明确，不要让 SQL Server 将字符串(来自 RIGHT 函数的时间部分)隐式转换为 DATETIME:

But UDFs are terribly slow. Instead, try being more explicit in your query and not leaving it to SQL Server to implicitly convert a string (the time portion coming out of the RIGHT function) into a DATETIME:

SELECT O.DeliveryDate + CONVERT(DATETIME, RIGHT(ISNULL(O.DeliveryTime, '00:00'), 5))
FROM MySuperTable;

这篇关于SQL Server 2008 R2 中奇怪的 CAST() 行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！