如何優雅的使用 參數 is null而不導致全表掃描(破壞索引)

相信大家在很多實際業務中(特別是後臺系統)會使用到各種篩選條件來篩選結果集

首先添加測試數據

CREATE TABLE TempList(Id int IDENTITY,Name VARCHAR(12),  Age INT)
go
CREATE INDEX idx_age ON TempList (Age)
GO


DECLARE @i INT;
SET @i=0;
WHILE @i<10000
BEGIN
  INSERT INTO TempList (Name, Age)VALUES(CAST(@i AS VARCHAR),@i)
  SET @i=@i+1;
END
GO

1.有使用EXEC來避免全表掃描

DECLARE @age INT=666;

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM TempList'

IF @age IS NOT NULL
    SET @sql = @sql + ' WHERE Age = @age'
    
EXEC sp_executesql @sql, N'@age int', @age

或者條件少的情況下

DECLARE @age INT=666;

IF @age IS NOT NULL
    SELECT * FROM TempList WHERE Age = @age
ELSE 
    SELECT * FROM TempList

2.使用IS NULL來實現

DECLARE @age INT=666;

SELECT * FROM TempList WHERE (@age IS NULL OR Age = @age)

SELECT * FROM TempList WHERE (Age = @age OR @age IS NULL)

SELECT * FROM TempList WHERE Age=isnull(@age, Age) 

第一種方案,不會破壞索引,但冗餘的代碼看起來讓人難受

第二種方案,會導致全表掃描(破壞索引)

以上是網上查閱的資料,方案二不符合SARG,問題在OR,如果我不使用OR,用AND呢

DECLARE @age INT=666;

SELECT * FROM TempList WHERE @age IS NOT NULL and Age = @age

版權聲明：本文原創發表於 博客園，作者為 托大人 本文歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，否則視為侵權。