以实例为例:
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,级别16,状态1,第1行转换时失败VARCHAR值" KEN"到数据类型int。
虽然以下查询执行没有错误:
SELECT [name]
FROM dbo.tblNames AS tn
WHERE ISNUMERIC([name]) = 1
AND [name] = 2541
我知道这是因为SQL Server查询优化器的决定。但是我想知道是否有任何方法可以使SQL Server按一定顺序评估条款。这样,在第一个查询中,第一个子句过滤出那些不是数字的Names
,因此第二子句不会失败转换为数字。
更新:,您可能会注意到,以上查询只是一个实例来体现问题的实例。我知道这种隐性转换的风险,并欣赏那些试图警告我的人。但是,我的主要问题是如何改变优化器按一定顺序评估子句的行为。
没有"直接" 告诉引擎按顺序执行操作的方法。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
)。
尝试像这样
SELECT [name]
FROM #TBL_Names AS tn
WHERE [name] IN ( SELECT [name]
FROM #TBL_Names
WHERE ISNUMERIC([name]) = 1 )
AND [name] = '2541'
2)
AND [name] = convert(varchar,2541 )
由于您将名称存储为varchar(32)varchar将接受整数数据类型值,也称为优先级值
at:
SELECT *
FROM dbo.tblNames AS tn
WHERE [name] = convert(varchar, 2541)
为什么您需要ISNUMERIC([name]) = 1)
,因为您只关心值'2541'
?
您可以尝试此
SELECT [name]
FROM dbo.TBL_Names AS tn
WHERE [name] IN ( SELECT [name]
FROM dbo.TBL_Names
WHERE ISNUMERIC([name]) = 1 )
AND [name] = '2541'
您只需要[name] = 2541
到[name] = '2541'
。您缺少 '
(单Quote),名称处的位置。
您可以在这里找到实时演示。
老实说,我不会将隐式铸件应用于您的列[name]
,它将使查询变得不可降低。而是将输入的值转换(或以字符串)
SELECT [name]
FROM dbo.TBL_Names tn
WHERE [name] = CONVERT(varchar(32),2541);
,如果您" 必须",但是,请包裹[name]
(并受苦性能降解),然后使用TRY_CONVERT
:
SELECT [name]
FROM dbo.TBL_Names tn
WHERE TRY_CONVERT(int,[name]) = 2541;