Ravindra Devrani

SQL TIP: stop using this condition in your query

`where some_column=@some_parameter or @some_parameter is NULL`

It does not use the index of `some_column` and leads to full table scan.

Example :

create or alter procedure GetBooks
@SearchTerm nvarchar(40)=null
as
begin
set nocount on;

select * from Book
where @SearchTerm is null or Title like @SearchTerm+'%';
end
go

-- execute the procedure
exec GetBooks @SearchTerm='The Epic Of Gilgamesh'; -- return 1 row

This query causes the full table scan.

You can solve it with:

1- Dynamic SQL
2- OPTION (RECOMPILE)

I have written an article and include previous community post on sql tip. You can find the solution using dynamic sql and option(recompile) there.

You can check this out:

ravindradevrani.medium.com/what-makes-your-query-n…

6 months ago | [YT] | 2