@n is null or n=@n 是否能保证效率？

@n is null or n=@n 这种写法是部分开发人员推崇的， 目的是避免SQL的拼接，避免动态SQL。

乍一看， 还是挺好的， 存储过程入口有一万个参数也无所谓， SQL语句里面全这么写上， 让SQL Server帮忙判断一下就是了。

但效率到底高不高， 还是得评测一下。

--注：按序号分开执行
--1. 
--------------- 测试数据 END ----------------
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	id INT IDENTITY(1,1) PRIMARY KEY,
	n1 NVARCHAR(50),
	n2 NVARCHAR(50)	
)
GO
;WITH cte AS (
	SELECT * FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 1000	
)
INSERT INTO t(n1,n2)
SELECT NEWID(),NEWID()
FROM cte AS a CROSS APPLY cte AS b
GO
--创建索引
CREATE INDEX ix_t_n1 ON t(n1)
--------------- 测试数据 END ----------------

--2. 
SET STATISTICS IO ON
SET STATISTICS TIME ON

DECLARE @n1 NVARCHAR(50)
SET @n1='9999'

SELECT * FROM t WHERE @n1 IS NULL OR n1=@n1
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(0 行受影响)
表 't'。扫描计数 1，逻辑读取 20486 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 172 毫秒，占用时间 = 176 毫秒。
*/

--3. 
DECLARE @n NVARCHAR(50)
SET @n='9999'

SELECT * FROM t WHERE n1=@n
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(0 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 't'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
*/

--4. 删除索引
DROP INDEX ix_t_n1 ON t

--5. 
DECLARE @n11 NVARCHAR(50)
SET @n11='9999'

SELECT * FROM t WHERE @n11 IS NULL OR n1=@n11
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(0 行受影响)
表 't'。扫描计数 1，逻辑读取 20486 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 187 毫秒，占用时间 = 187 毫秒。
*/
--6. 
DECLARE @n12 NVARCHAR(50)
SET @n12='9999'

SELECT * FROM t WHERE n1=@n12
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(0 行受影响)
表 't'。扫描计数 5，逻辑读取 20784 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 172 毫秒，占用时间 = 67 毫秒。
*/

有索引时的执行计划：

无索引时的执行计划：

不怕不识货， 就怕货比货， 一看就高下立判了， @n is null or n=@n 这种方式还是不可取，无论有无索引，都是低效的。

另外在无索引时， 相比 @n11 is null or n1 =@n11 ， 下面这个查询按IO来看， 扫描更多， 为什么效率更高呢？

答案就在执行计划， 查询2比查询1 多了一个 Parallelism ， 也就是并行度， 查询并发操作以提高性能。

附：强制让查询1使用并行操作，也是能改变其执行计划的，但消耗还是大一点：

--7. 强制并行
DECLARE @n13 NVARCHAR(50)
SET @n13='9999'

SELECT * FROM t WHERE @n13 IS NULL OR n1=@n13 OPTION(MAXDOP 0,querytraceon 8649)
/*
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(0 行受影响)
表 't'。扫描计数 5，逻辑读取 20784 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 SQL Server 执行时间:
   CPU 时间 = 217 毫秒，占用时间 = 84 毫秒。
*/

参考：msdn