在 SQL Server 中，not(columnName='value') 和 columnName&
时间：2022-11-04问题描述
在 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' 之间有什么区别吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
相关文章
什么是 SQL“伪列"?What is a SQL quot;pseudocolumnquot;?(什么是 SQL“伪列?)- 如何使用 SQL 查询显示像父子项一样的层次结构行How to show hierarchy row like parent child using SQL query(如何使用 SQL 查询显示像父子项一样的层次结构行)
- SQL Server 根据不同的标志计算累积总和/条件运行总和SQL Server Calculate cumulative sum / conditional running total depending on different flags(SQL Server 根据不同的标志计算累积总和/条件运行总和)
- SQL Server getdate() 转换为类似“2009-12-20"的字符串SQL Server getdate() to a string like quot;2009-12-20quot;(SQL Server getdate() 转换为类似“2009-12-20的字符串)
- SQL在删除子表行时锁定父表SQL locks parent table while deleting child table row(SQL在删除子表行时锁定父表)
- T-SQL:从 text 或 ntext 列中选择前 n 个字符T-SQL: Selecting top n characters from a text or ntext column(T-SQL:从 text 或 ntext 列中选择前 n 个字符)