問題描述
在 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)
這篇關于在 SQL Server 中，not(columnName='value') 和 columnName<>'value' 之間有什么區別嗎?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！