在 SQL Server 中，not(columnName='value') 和 columnName<>'value' 之间有什么区别吗?

2021-09-10 00:00:00 sql tsql sql-server

在 SQL Server where 子句中，您编码 not(columnName='value') 或 columnName<>'value' 有什么区别吗?>

我在考虑性能.

有人告诉我，当使用 Not() 时，它可能不会使用它可能与 <> 一起使用的索引.

解决方案

最好的办法是检查执行计划.当我在 SQL Server 2008 中测试以下内容时，它们给出了相同的计划(并且都被转换为 2 个范围搜索.所以 <>x 被转换为 >x 或)

创建表T(C INT,D INT,主键(C, D))插入 T选择 1,1联合所有选择 DISTINCT 2,数字FROM master..spt_values选择 *从T如果不是(C = 2)选择 *从T哪里(C <2 )

给予

 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))|--合并间隔||--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))||--计算标量(DEFINE:([Expr1013]=((4)&[Expr1012]) = (4) AND NULL = [Expr1010], [Expr1014]=(4)&[Expr1012], [Expr1015]=(16)&[Expr1012]))||--串联||--计算标量(DEFINE:([Expr1005]=NULL, [Expr1006]=CONVERT_IMPLICIT(int,[@1],0), [Expr1004]=(10)))|||--持续扫描||--计算标量(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[@1],0), [Expr1009]=NULL, [Expr1007]=(6)))||--持续扫描|--聚集索引查找(OBJECT:([test].[dbo].[T].[PK__T__B86D18326339AFF7]), SEEK:([test].[dbo].[T].[C] > [Expr1010]AND [test].[dbo].[T].[C] < [Expr1011]) ORDERED FORWARD)

In a SQL Server where clause does it make any difference whether you code not(columnName='value') or columnName<>'value'?

I am thinking in terms of performance.

I have been told that when using Not() it might not use an index that it might otherwise use with <>.

解决方案

Best thing to do is to check the execution plans. When I test the following in SQL Server 2008 they give identical plans (and both get translated into 2 range seeks. So <> x gets converted to > x OR < x)

CREATE TABLE T
  (
     C INT,
     D INT,
     PRIMARY KEY(C, D)
  )

INSERT INTO T
SELECT 1,
       1
UNION ALL
SELECT DISTINCT 2,
                number
FROM   master..spt_values

SELECT *
FROM   T
WHERE  NOT ( C = 2 )

SELECT *
FROM   T
WHERE  ( C <> 2 )

Gives

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
       |--Merge Interval
       |    |--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))
       |         |--Compute Scalar(DEFINE:([Expr1013]=((4)&[Expr1012]) = (4) AND NULL = [Expr1010], [Expr1014]=(4)&[Expr1012], [Expr1015]=(16)&[Expr1012]))
       |              |--Concatenation
       |                   |--Compute Scalar(DEFINE:([Expr1005]=NULL, [Expr1006]=CONVERT_IMPLICIT(int,[@1],0), [Expr1004]=(10)))
       |                   |    |--Constant Scan
       |                   |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[@1],0), [Expr1009]=NULL, [Expr1007]=(6)))
       |                        |--Constant Scan
       |--Clustered Index Seek(OBJECT:([test].[dbo].[T].[PK__T__B86D18326339AFF7]), SEEK:([test].[dbo].[T].[C] > [Expr1010] AND [test].[dbo].[T].[C] < [Expr1011]) ORDERED FORWARD)

相关文章