SQL WHERE variable IS NULL causes full table scan Problems

SQL WHERE variable IS NULL causes full table scan Problems
Today, when reviewing the stored procedures of the project, we found that there are a large number of statements to judge whether the variable is NULL in the condition, such:
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:
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:
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:
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:
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:
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.