Solution to the full table scan problem caused by the WHERE variable is null in SQL

Copy codeThe Code is as follows:
SET @ SQL = 'select * FROM Comment with (nolock) WHERE 1 = 1
And (@ ProjectIds Is Null or ProjectId = @ ProjectIds)
And (@ Scores is null or Score = @ Scores )'

I remember that when I was doing Oracle development, this method would scan the entire table and could not use the index. I don't know if it is the same in SQL Server, so I made a simple test.
1. Create a table structure and index for testing:
Copy codeThe Code is as follows:
Create table aaa (id int IDENTITY, name varchar (12), age INT)
Go
Create index idx_age ON aaa (age)
GO

2. Insert 10 thousand pieces of test data:
Copy codeThe Code is as follows:
DECLARE @ I INT;
SET @ I = 0;
WHILE @ I <10000
BEGIN
Insert into aaa (name, age) VALUES (CAST (@ I as varchar), @ I)
SET @ I = @ I + 1;
END
GO

3. enable the execution plan display first:
In the SQL Server Management Studio query window, right-click any position in the window and select "include actual execution plan ":

4. Start the test and use the following SQL to perform the test:
Copy codeThe Code is as follows:
DECLARE @ I INT;
SET @ I = 100
SELECT * FROM aaa WHERE (@ I is null or age = @ I)
SELECT * FROM aaa WHERE (age = @ I OR @ I IS NULL)
SELECT * FROM aaa WHERE age = isnull (@ I, age)
SELECT * FROM aaa WHERE age = @ I

The test results are as follows:

You can see that even if @ I has a value, no matter whether @ I is null is placed in front or behind, the index of age cannot be used. In addition, age = ISNULL (@ I, age) you cannot use the index.

The final conclusion IS that SQL Server, like ORACLE, will scan the entire table if the variable IS NULL IS added to the condition.

We recommend that you change the SQL statement:
Copy codeThe Code is as follows:
DECLARE @ I INT;
SET @ I = 100

DECLARE @ SQL NVARCHAR (MAX)
SET @ SQL = 'select * FROM aaa'
IF @ I IS NOT NULL
SET @ SQL = @ SQL + 'where age = @ I'
EXEC sp_executesql @ SQL, n' @ I int', @ I

Of course, if there is only one condition, you can design two SQL statements, for example:
Copy codeThe Code is as follows:
DECLARE @ I INT;
SET @ I = 100
IF @ I IS NOT NULL
SELECT * FROM aaa WHERE age = @ I
ELSE
SELECT * FROM aaa

However, if there are more conditions, the number of SQL statements increases. Therefore, we recommend that you use the EXEC solution.