Solution to a full table scan problem in SQL where the where variable is null condition _mssql

Copy Code code as follows:

SET @SQL = ' SELECT * Comment with (NOLOCK) WHERE 1=1
and (@ProjectIds is Null or ProjectID = @ProjectIds)
and (@Scores is null or Score = @Scores) '

Remember, in the past when doing Oracle development, this writing will lead to a full table scan, not indexed, do not know whether SQL Server is the same, so do a simple test
1, set up the test table structure and index:

Copy Code code as follows:

CREATE TABLE aaa (id int IDENTITY, NAME VARCHAR (), age int)
Go
CREATE INDEX Idx_age on AAA (age)
Go

2, insert 10,000 test data:

Copy Code code as follows:

DECLARE @i INT;
SET @i=0;
While @i<10000
BEGIN
INSERT into Triple A (name, age) VALUES (CAST (@i as VARCHAR), @i)
SET @i=@i+1;
End
Go

3, first open the implementation plan display:
In the SQL Server Management Studio Query window, right-click anywhere in the window and select include Actual execution plan:

4. Start the test and test with the following SQL:

Copy Code code as follows:

DECLARE @i INT;
SET @i=100
SELECT * from AAA WHERE (@i are 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:

As you can see, even if @i has a value, the index of age cannot be used, regardless of whether @i is null in front or behind, and Age=isnull (@i,age) is not indexed

Finally, SQL Server, like Oracle, would cause a full table scan if the condition was added with variable is NULL.

We recommend that SQL be changed to:

Copy Code code 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, it can be designed into 2 SQL, such as:

Copy Code code 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 and the number of SQL becomes more, it is recommended to use the exec scheme