主页 / dba / 问题 / 182728
Accepted
IronicMuffin
Asked: 2017-08-05 10:02:19 +0800 CST

为什么 SQL 会尝试转换我的 where 子句排除的行？

  • 772

我已经多次遇到这种情况，我确信这是有充分理由的——但我该如何避免呢？

我敢肯定这与周围的怪癖有关isnumeric。在英语中，我有一个过滤isnumeric(somefield) = 1. 然后我尝试int在我的where子句中使用 an 来查询它，并且因为表中的其他一些字段具有字符值，所以整个事情都失败了。

我创建了一个显示错误的SQL 小提琴。

我尝试在cast/convert视图上进行选择，但查询引擎似乎忽略了它。

那么 - 为什么会发生这种情况，有没有一种干净的方法来处理它？

由于我在插入时缺少引号，原来的小提琴有一个非常相似的错误。这不是我试图强调的问题。我通过为所有插入的值添加引号来修复视图。

sql-server view

7 个回答

  1. Best Answer

    该视图没有被使用，因为它正在被查询优化器“优化掉”。您可以通过查看查询计划看到这一点：

    在此处输入图像描述

    您可以创建索引物化视图，然后在目标查询上使用NOEXPAND表提示来防止这种优化。

    一个例子：

    USE tempdb;

IF OBJECT_ID(N'dbo.TestView', N'V') IS NOT NULL
BEGIN
    DROP VIEW dbo.TestView;
    DROP TABLE dbo.Test;
END
CREATE TABLE dbo.Test 
(
    TableKey int
    , TestField nvarchar(24)
);
GO

    使用 SCHEMABINDING 创建一个视图：

    CREATE VIEW dbo.TestView 
WITH SCHEMABINDING
AS
    SELECT 
        t.TableKey
        , t.TestField
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;
GO

    在视图上创建索引：

    CREATE UNIQUE CLUSTERED INDEX PK_TestView
ON dbo.TestView (TableKey);
GO

    插入测试数据：

    INSERT INTO dbo.Test
select 0,        '0'
union select 1,  'Rejected'
union select 2,  'Unlinked'
union select 0,  '0'
union select 3,  '1'
union select 162,'1000'
union select 16, '10000'
union select 17, '10010'
union select 18, '10011'
union select 19, '10012'
union select 20, '10031'
union select 21, '10041'

    查询视图：

    SELECT * 
FROM dbo.TestView WITH (NOEXPAND)
WHERE Testfield = 1000

    结果：

    ╔══════════╦═══════════╗
║ 表键 ║ 测试字段 ║
╠══════════╬═══════════╣
║ 162 ║ 1000 ║
╚══════════╩═══════════╝

    查询计划：

    在此处输入图像描述

    请注意上面查询计划中 SELECT 节点中的三角形感叹号 - 这是一个警告：

    表达式中的类型转换 (CONVERT_IMPLICIT(int,[tempdb].[dbo].[TestView].[TestField],0)) 可能会影响查询计划选择中的“CardinalityEstimate”，表达式中的类型转换 (CONVERT_IMPLICIT(int,[tempdb] .[dbo].[TestView].[TestField],0)=CONVERT_IMPLICIT(int,[@1],0)) 可能会影响查询计划选择中的“SeekPlan”

    如果我们像这样修改视图，则可以消除类型转换警告：

    CREATE VIEW dbo.TestView 
WITH SCHEMABINDING
AS
    SELECT 
        t.TableKey
        , TestField = TRY_CONVERT(int, t.TestField)
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;

    现在，视图将返回TestField类型为整数的列，并且由于我们NOEXPAND与持久数据结合使用，因此消除了隐式类型转换：

    在此处输入图像描述

    • 9

  2. 正如其他答案所解释的那样，问题出在INSERT语句上，并且 - 当它被修复时 - 视图被优化掉了。

    结果是查询where Testfield = 1000将尝试将所有行以及列的所有值强制TestField转换为数字（由于数据类型优先规则）。

    执行顺序很难强制执行，因为优化器可以自由地重写查询，并且不保证标量表达式的计算时间、顺序或数量。

    解决此问题的一种方法是使用CASE表达式。仍然不能绝对保证避免此类问题，但它几乎总是有效。在sqlfiddle.com中测试：

    CREATE VIEW dbo.TestView 
AS
  SELECT  
    TableKey,
    TestField = CASE WHEN IsNumeric(TestField) = 1 THEN TestField END
  FROM
    dbo.Test
  WHERE
    IsNumeric(TestField) = 1 ;

    另请参阅 Aaron Bertrand 的这个答案以获得更详细的解释：CTE 错误（nvarchar 到数字）TRY_CONVERT()以及如果您使用的是最新版本，则建议使用更健壮的。

    • 7

  3. 好的 - 我的问题可能不清楚，但我想做的主要事情是在不必使用引号的情况下查询我的视图。

    try_convert多亏了较新版本的 SQL 和函数，我能够实现这一点。如果我将它添加到我对相关字段的视图中，我可以在不使用引号的情况下成功查询它。

    小提琴显示变化。

    奇怪的是，小提琴没有返回结果——但我的实际代码是这样工作的，所以我称之为胜利。

    • 2

  4. 它与视图无关。这与工会有关。SQL Server 正在键入您的并集，(int, int)它需要将其键入到(int, nvarchar(24)). 虽然违反直觉，但联合中的类型总是很有趣。在您的示例中，插入实际上失败了。

    要解决此问题，只需确保您查询 TestField 就好像它是一个文本列（因为那是您所做的），并在插入中确保您正确键入了值，以免混淆 SQL Server。

    insert into dbo.Test
select 0,   '0'
union select 1, 'Rejected'
union select 2, 'Unlinked'
union select 0, '0'
union select 3, '1'
union select 162,   '1000'
union select 16,    '10000'
union select 17,    '10010'
union select 18,    '10011'
union select 19,    '10012'
union select 20,    '10031'
union select 21,    '10041'
;
select * from dbo.Test
where Testfield = '1000';
    • 1

  5. 我为您的sqlfiddle.com 链接创建了一个稍微修改过的版本。

    数据填充正确，可以使用与视图中相同的语句进行选择，但从id= 1000 的视图中选择仍然失败。

    为什么？这是优化器的错。

    优化器看穿了视图，并且基本上处理：

    SELECT TableKey, TestField
  FROM dbo.TestView
 WHERE TestField = 1000
;

    好像是：

    SELECT TableKey, TestField
  FROM dbo.Test
 WHERE IsNumeric(TestField) = 1
   AND TestField = 1000
;

    当它进行优化时，它必须决定哪个可以更好地限制返回的数据 - 的基数TestField比IsNumeric(TestField).

    我承认，我猜到了。它可能会忽略该函数，因为只有一个数值可以匹配，或者只是简单地评估它，因为如果不扫描表并运行函数就无法轻松确定函数结果的基数。

    尽管如此，基本现实是优化器不会首先过滤掉非数字行，即使这是您希望它做的事情。

    如果您将视图设为索引视图并使用NOEXPAND表提示，那么您可能会得到您期望的结果；优化器应该拒绝直接进入表，并使用视图上的索引来满足它的需要。

    最简单的解决方案是避免隐式转换。如果您不离开哪一方来转换为 SQL，那么您就可以控制正在发生的事情。

    在 SQL 2012 之前，强制输入字符串（使用'1000'代替1000）可能是您最简单的解决方案。

    从 SQL 2012 开始，您可以使用TRY_CONVERT（请参阅sqlfiddle中的最后一个查询）。这将在可以返回的地方返回一个转换后的值，在不能的地方返回一个 NULL。当然，到那时，您可以取消视图。

    所以：

    1. SQL Server 不会将视图视为表，并且可以重新排列它们，因此WHERE不会在查询子句之前评估视图WHERE子句。
    2. 不要相信隐式转换会按照你想要的方式做你想做的事。
    • 1

  6. 您需要使用以下方法短路测试而不是值case：

      SELECT TableKey, TestField
  FROM dbo.Test
 WHERE 
   case when isnumeric(testfield)=1 then testfield else null end = 1000

    推杆

    case when isnumeric(testfield)=1 then testfield else null end as newTestField

    在你的观点和测试中select也可以。

    • 1

  7. 让我们从根本上说。

    • 视图 - 以您的方式 - 在运行时进行评估。
    • 为了检查过滤器，它必须比较每一行是否为数字。视图中没有“优化”。该视图将强制转换每一行以检查数字。
    • 与您的查询相同。查询使视图变得多余——因此逻辑上消除了检查。但是当您与数值进行比较时，您仍然必须转换所有行。

    典型的初学者陷阱。字段上的函数（通常）意味着没有优化（除非在计算字段上使用索引等功能）。这种情况下的转换强制对每一行进行表扫描和转换。糟糕的表格和数据设计又回来咬你了。

    正如RDFozz 所说，您最好的方法是强制进行LIKE '1000'数据原生的字符串比较 ()。

    • 1

相关问题