SQL Server JOIN中的IsNumeric [英] IsNumeric in SQL Server JOIN

问题描述

我的问题似乎很简单，但是我被困在这里.我有一个表，其中有一个名为"SrcID"的"nvarchar"列，并且在其中存储了数字和字符串.现在，当我尝试在加入"条件下检查该列上的"IsNumeric"时，如下所示，

My problem seems to be very simple but I'm stuck here. I have a table which has an "nvarchar" column called "SrcID" and I store both numbers and strings in that. Now, when I try to check for "IsNumeric" on that column in a "Join" condition, something like below,

   ISNUMERIC(SrcID) = 1 AND SrcID > 15

我遇到以下错误:

  Msg 245, Level 16, State 1, Line 47
  Conversion failed when converting the nvarchar value 'Test' to data type int.

令人惊讶的是，当我取消选中"SrcID> 15"时，我的查询运行正常.我是否应该在此声明中添加其他内容?

Amazingly, when I remove the check "SrcID > 15", my query is running properly. Should I include anything else in this statement?

请帮助我解决此问题.在此先感谢！

Please help me in fixing the issue. Thanks in advance!!

推荐答案

您不能指望数据库评估过滤表达式的顺序.有一个查询优化器，它将评估您的SQL并根据其认为将产生最佳性能的内容来制定执行查询的计划.

You can't count on the order in which a database will evaluate filtering expressions. There is a query optimizer that will evaluate your SQL and build a plan to execute the query based on what it perceives will yield the best performance.

在这种情况下，IsNumeric()不能与索引一起使用，这意味着对表中的每一行都运行一个函数.因此，它几乎不会提供最佳的感知性能.将此结果与SrcID > 15表达式进行比较，该表达式可以与索引匹配(如果存在)，并且即使不存在，也只是一个运算符表达式.它也可以用来过滤IsNumeric()函数需要运行的潜在行数.

In this context, IsNumeric() cannot be used with an index, and it means running a function against every row in the table. Therefore, it will almost never provide the best perceived performance. Compare this with the SrcID > 15 expression, which can be matched with an index (if one exists), and is just a single operator expression even if one doesn't. It can also be used to filter down the number of potential rows where the IsNumeric() function needs to run.

您可能可以通过视图，子查询，CTE，CASE语句或计算列来解决此问题.这是CTE示例:

You can likely get around this with a view, a subquery, a CTE, a CASE statement, or a computed column. Here's a CTE example:

With NumericOnly As 
(
    SELECT <columns> FROM MyTable WHERE IsNumeric(SrcID) = 1
)
SELECT <columns> FROM NumericOnly WHERE SrcID > 15

这是一个CASE语句选项:

And here's a CASE statement option:

SELECT <columns> FROM MyTable WHERE CASE WHEN IsNumeric(SrcIC) = 1 THEN Cast(SrcID As Int) ELSE 0 END > 15

这篇关于SQL Server JOIN中的IsNumeric的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！