sql - 使 SQL SERVER 以特定顺序评估子句

Question

以下表为例:

CREATE TABLE TBL_Names(Name VARCHAR(32))

INSERT INTO TBL_Names
VALUES ('Ken'),('1965'),('Karen'),('2541')

sqlfiddle

执行以下查询会抛出异常:

SELECT  [name]
FROM    dbo.tblNames AS tn
WHERE   [name] IN ( SELECT  [name]
                    FROM    dbo.tblNames
                    WHERE   ISNUMERIC([name]) = 1 )
        AND [name] = 2541

Msg 245, Level 16, State 1, Line 1 Conversion failed when convertingthe varchar value 'Ken' to data type int.

虽然下面的查询没有错误地执行:

SELECT  [name]
FROM    dbo.tblNames AS tn
WHERE   ISNUMERIC([name]) = 1
        AND [name] = 2541

我知道这是因为 SQL Server 查询优化器的决定。但我想知道是否有任何方法可以使 sql server 按特定顺序评估子句。这样，在第一个查询中，第一个子句过滤掉那些不是数字的 Names，这样第二个子句就不会在转换为数字时失败。

更新:您可能已经注意到，上面的查询只是一个示例问题。我知道这种隐式转换的风险，并感谢那些试图警告我的人。但是，我的主要问题是如何更改优化器按特定顺序评估子句的行为。

Answer 1

没有“直接”方式告诉引擎按顺序执行操作。 SQL 不是一种您可以完全控制如何做事的命令式语言，您只需告诉什么您需要，服务器自行决定如何处理。

对于这种特殊情况，只要您有 [name] = 2541 , 因为您正在比较 VARCHAR，所以您面临潜在的转换失败风险针对 INT 的列.即使您使用子查询/CTE，优化器仍有空间首先评估此表达式并尝试将所有 varchar 值转换为 int(因此失败)。

您可以通过变通办法避免这种情况:

• 正确比较匹配的数据类型:

  [name] = '2541'
  

• 选角 [name]至 INT事先且仅在可能的情况下在不同的语句上进行比较。

  DECLARE @tblNamesInt TABLE (nameInt INT)
  
  INSERT INTO @tblNamesInt (
      nameInt)
  SELECT
      [nameInt] = CONVERT(INT, [name])
  FROM    
      dbo.tblNames
  WHERE   
      TRY_CAST([name] AS INT) IS NOT NULL -- TRY_CAST better than ISNUMERIC for INT
  
  
  SELECT
      *
  FROM
      @tblNamesInt AS T
  WHERE
      T.nameInt = 2351 -- data types match
  

即使是索引提示也不会强制优化器使用索引(这就是它被称为提示的原因)，因此我们几乎无法控制它如何完成工作。

---

我们知道有一些机制是按顺序评估的，我们可以利用我们的优势，例如 HAVING表达式将始终在分组值之后计算，并且分组始终在 WHERE 之后条件。所以我们可以“安全地”进行以下分组:

DECLARE @Table TABLE (IntsAsVarchar VARCHAR(100))

INSERT INTO @Table (IntsAsVarchar)
VALUES
    ('1'), 
    ('2'),
    ('20'),
    ('25'),
    ('30'),

    ('A') -- Not an INT!

SELECT
    CASE WHEN T.IntsAsVarchar < 15 THEN 15 ELSE 30 END,
    COUNT(*)
FROM
    @Table AS T
WHERE
    TRY_CAST(T.IntsAsVarchar AS INT) IS NOT NULL -- Will filter out non-INT values first
GROUP BY
    CASE WHEN T.IntsAsVarchar < 15 THEN 15 ELSE 30 END

但是您应该始终避免编写暗示隐式转换的代码(例如 T.IntsAsVarchar < 15 )。