blocks|key|2439873|text|试着像这样|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2439874|++SELECT++[name]
++++FROM++++#TBL_Names+AS+tn
++++WHERE+++[name]+IN+(+SELECT++[name]
++++++++++++++++++++++++FROM++++#TBL_Names
++++++++++++++++++++++++WHERE+++ISNUMERIC([name])+=+1+)
++++++++++++AND++[name]+=+'2541'|code-block|syntax|javascript|2439875|2)|2439876|AND++[name]+=++convert(varchar,2541+)|2439877|由于将名称存储为varchar(32)，varchar将接受整数数据类型值(也称为优先级值)|2439878|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Try like this 

<pre><code> SELECT [name]
 FROM #TBL_Names AS tn
 WHERE [name] IN ( SELECT [name]
 FROM #TBL_Names
 WHERE ISNUMERIC([name]) = 1 )
 AND [name] = '2541'
</code></pre>

2)

<pre><code>AND [name] = convert(varchar,2541 )
</code></pre>

Since You are storing name as varchar(32) varchar will accept integer datatype values also called precedence value

blocks|key|1030971|text|你可以试试这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1030972|SELECT++[name]
FROM++++dbo.TBL_Names+AS+tn
WHERE+++[name]+IN+(+SELECT++[name]
++++++++++++++++++++FROM++++dbo.TBL_Names
++++++++++++++++++++WHERE+++ISNUMERIC([name])+=+1+)
++++++++AND+[name]+=+'2541'|code-block|syntax|javascript|1030973|你只需要从[name]+=+2541到[name]+=+'2541'。您缺少了'+(单引号)，在where条件下有名称。|offset|length|style|CODE|1030974|您可以找到现场演示这里。|1030975|entityMap|0|LINK|mutability|MUTABLE|url|https://rextester.com/TVYK22662^0|0|0|5|D|J|F|13|1|0|9|2|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@$I|Z|J|10|K|L]|$I|11|J|12|K|L]|$I|13|J|14|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|15|8|@]|9|@$I|16|J|17|1|18]]|A|$]]|$1|O|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

You can try this

<pre><code>SELECT [name]
FROM dbo.TBL_Names AS tn
WHERE [name] IN ( SELECT [name]
 FROM dbo.TBL_Names
 WHERE ISNUMERIC([name]) = 1 )
 AND [name] = '2541'
</code></pre>

You need to just <code>[name] = 2541</code> to <code>[name] = '2541'</code>. You are missing <code>'</code> (single quote) with name in where condition.

You can find the live demo <a href="https://rextester.com/TVYK22662" rel="nofollow noreferrer">Here</a>.

blocks|key|1030977|text|那麽：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1030978|SELECT+*
FROM+dbo.tblNames+AS+tn
WHERE+[name]+=+convert(varchar,+2541)|code-block|syntax|javascript|1030979|您为什么需要ISNUMERIC([name])+=+1)，因为您只关心值'2541'|offset|length|style|CODE|1030980|entityMap^0|0|0|6|M|10|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

What about:

<pre><code>SELECT *
FROM dbo.tblNames AS tn
WHERE [name] = convert(varchar, 2541)
</code></pre>

Why do you need <code>ISNUMERIC([name]) = 1)</code> since you only care about the value <code>'2541'</code>?

blocks|key|1034051|text|没有“直接”的方式告诉引擎按照顺序执行操作。SQL不是一种命令式语言，在这种语言中，您可以完全控制如何来做事情，您只需告诉您需要什么，而服务器决定如何自己完成它。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1034052|对于这种特殊情况，只要您有[name]+=+2541，就会面临潜在的转换失败的风险，因为您是在比较VARCHAR列和INT。即使使用子查询/CTE，优化器仍有空间先计算这个表达式，然后尝试将所有varchar值转换为int+(因此失败)。|CODE|1034053|你可以用一些变通的方法来逃避这个问题：|1034054|正确比较匹配的数据类型：
名称=+'2541‘|unordered-list-item|1034055|事先并仅在可能的情况下将[name]转换为INT，并在不同的语句上进行比较。
声明@tblNamesInt表(nameInt+INT)插入到@tblNamesInt+(+nameInt)中，选择nameInt+=从dbo.tblNames中转换(+INT、name)，其中TRY_CAST+(名称为INT)不是NULL+--+TRY_CAST优于INT+SELECT+*+FROM+*+FROM+@tblNamesInt，其中T.nameInt+=+2351+--数据类型匹配|1034056|即使是索引提示也不会迫使优化器使用索引(这就是为什么它被称为提示)，因此我们对它如何完成任务几乎没有控制。|1034057|我们知道有几种机制是按顺序计算的，我们可以利用它们的优势，例如HAVING表达式总是在分组值之后计算，分组总是在WHERE条件之后。因此，我们可以“安全”地进行以下分组：|1034058|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|code-block|syntax|javascript|1034059|但是，您应该始终避免编写隐含转换的代码(如T.IntsAsVarchar+<+15)。|1034060|entityMap^0|1D|2|1P|5|0|D|D|1D|7|1M|3|0|0|0|C|6|L|3|0|0|V|6|1K|5|0|0|L|K|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|17|8|@$9|18|A|19|B|H]|$9|1A|A|1B|B|H]|$9|1C|A|1D|B|H]]|D|@]|E|$]]|$1|I|3|J|5|6|7|1E|8|@]|D|@]|E|$]]|$1|K|3|L|5|M|7|1F|8|@]|D|@]|E|$]]|$1|N|3|O|5|M|7|1G|8|@$9|1H|A|1I|B|H]|$9|1J|A|1K|B|H]]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1L|8|@]|D|@]|E|$]]|$1|R|3|S|5|6|7|1M|8|@$9|1N|A|1O|B|H]|$9|1P|A|1Q|B|H]]|D|@]|E|$]]|$1|T|3|U|5|V|7|1R|8|@]|D|@]|E|$W|X]]|$1|Y|3|Z|5|6|7|1S|8|@$9|1T|A|1U|B|H]]|D|@]|E|$]]|$1|10|3|-4|5|6|7|1V|8|@]|D|@]|E|$]]]|11|$]]

There is no "direct" way of telling the engine to perform operations in order. SQL isn't an imperative language where you have complete control of how to do things, you simply tell what you need and the server decides how to do it itself.

For this particular case, as long as you have <code>[name] = 2541</code>, you are risking a potential conversion failure since you are comparing a <code>VARCHAR</code> column against an <code>INT</code>. Even if you use a subquery/CTE there is still room for the optimizer to evaluate this expression first and try to convert all varchar values to int (thus failing).

You can evade this with workarounds:

<ul>
<li>Correctly comparing matching data types:

<pre><code>[name] = '2541'
</code></pre></li>
<li>Casting <code>[name]</code> to <code>INT</code> beforehand and only whenever possible and on a different statement, do the comparison.

<pre><code>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
</code></pre></li>
</ul>

Even an index hint won't force the optimizer to use an index (that's why it's called a hint), so we have little control on how it gets stuff done.

<hr>

There are a few mechanics that we know are evaluated in order and we can use to our advantage, such as the <code>HAVING</code> expressions will always be computed after grouping values, and the grouping always after <code>WHERE</code> conditions. So we can "safely" do the following grouping:

<pre><code>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 &lt; 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 &lt; 15 THEN 15 ELSE 30 END
</code></pre>

But you should always avoid writing code that implies implicit conversions (like <code>T.IntsAsVarchar &lt; 15</code>).

blocks|key|1034071|text|老实说，我不会将隐式强制转换应用于您的列[name]，这将使查询非SARGable。相反，转换输入的值(或将其作为字符串传递)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1034072|SELECT+[name]
FROM+dbo.TBL_Names+tn
WHERE+[name]+=+CONVERT(varchar(32),2541);|code-block|syntax|javascript|1034073|但是，如果您“必须”，则包装[name]+(并遭受性能下降)，然后使用TRY_CONVERT|1034074|SELECT+[name]
FROM+dbo.TBL_Names+tn
WHERE+TRY_CONVERT(int,[name])+=+2541;|1034075|entityMap^0|K|6|0|0|E|6|Z|B|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

Honestly, I wouldn't apply the implicit cast to your column <code>[name]</code>, it'll make the query non-SARGable. Instead, convert the value of your input (or pass it as a string)

<pre><code>SELECT [name]
FROM dbo.TBL_Names tn
WHERE [name] = CONVERT(varchar(32),2541);
</code></pre>

If you "must", however, wrap <code>[name]</code> (and suffer performance degradation) then use <code>TRY_CONVERT</code>:

<pre><code>SELECT [name]
FROM dbo.TBL_Names tn
WHERE TRY_CONVERT(int,[name]) = 2541;
</code></pre>

Take the following table as an instance:
<pre><code>CREATE TABLE TBL_Names(Name VARCHAR(32))

INSERT INTO TBL_Names
VALUES ('Ken'),('1965'),('Karen'),('2541')
</code></pre>
<a href="http://sqlfiddle.com/#!18/9eecb" rel="nofollow noreferrer">sqlfiddle</a>
Executing following query throws an exception:
<pre><code>SELECT [name]
FROM dbo.tblNames AS tn
WHERE [name] IN ( SELECT [name]
 FROM dbo.tblNames
 WHERE ISNUMERIC([name]) = 1 )
 AND [name] = 2541
</code></pre>
<blockquote>
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting
the varchar value 'Ken' to data type int.
</blockquote>
While the following query executes without error:
<pre><code>SELECT [name]
FROM dbo.tblNames AS tn
WHERE ISNUMERIC([name]) = 1
 AND [name] = 2541
</code></pre>
I know that this is because of SQL Server Query Optimizer's decision. but I am wondering if there is any way to make sql server evaluate clauses in a certain order. this way, in the first query,the first clause filters out those <code>Names</code> that are not numeric so that the second clause will not fail at converting to a number.
Update: As you may noticed, the above query is just an instance to exemplify the problem. I know the risks of that implicit conversion and appreciate those who tried to warn me of that. However my main question is how to change Optimizer's behavior of evaluating clauses in a certain order.

Make SQL SERVER evaluate clauses in a certain order

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

以下表为例：CREATE TABLE TBL_Names(Name VARCHAR(32))INSERT INTO TBL_NamesVALUES ('Ken'),('1965'),('Karen'),('2541')执行以下查询会引发异常：SELECT  [name]FROM    dbo.tblNames AS t...

问按一定顺序使SQL SERVER计算子句
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问按一定顺序使SQL SERVER计算子句EN