Excellent article. I must admit that I was extreemly skeptical at first as I am used to writing catch all phrases in my code which I thought was so clever before.
SELECT number FROM dbo.tbl WHERE name=ISNULL(@name,name);
Now I realize after running a test that this kind of "catch-all" coding as described by Gail can be a performance killer. I had to share my test findings because the test is so simple, but the results are dramatic.
I'm querying a table in our test environment with over 700,000 rows. The two columns I'm querying consist of the submission number qte and policy number pol. The submission number is the primary key on a clustered index while the policy is on a nonclustered index and could be a NULL value. Searching for NULL values in pol returns roughly half the rows in the table. A valid pol value will return at most 2 rows.
Try the old "catch-all" method.
DECLARE @p varchar(100);SET @p='p12345';
SELECT qte FROM dbo.tbl WHERE pol=ISNULL(@p,pol);
The actual execution plan shows an index scan is performed on tbl with an estimated subtree cost of 6.19. The exact same query plan is used when I assign NULL to the @p parameter.
Try using dynamic sql.
DECLARE @sql nvarchar(1000),@where nvarchar(1000),@params nvarchar(1000),@p varchar(100);
SET @sql=N'SELECT qte FROM dbo.tbl ';
SET @params=N'@pol varchar(100)';
IF @p IS NOT NULL
SET @where='WHERE pol=@pol;'
SET @where='WHERE pol IS NULL;';
EXEC sp_executesql @sql,@params,@pol=@p;
The actual execution plan uses an index seek on both the clustered index and the nonclustered index for a total subtree cost of... wait for it... .00657 :w00t:. When I pass a NULL value for the parameter it switches back to an index scan with the same cost as seen in Step 1.
What an eye opener. Almost a 1000% increase in performance for non-NULL parameters. Previously, I looked at dynamic sql as something to be feared and shunned, but it definitely has its place.
I think the argument about disallowing a NULL value from being passed in the first place is a valid one, but we are still all at the mercy of app developers and end users requesting reports with wide-ranging functionality.