October 17, 2011 at 2:08 pm
Hi Friends,
I just wanted to discuss the best practice to work with isnull and want inputs from fellow group members.
Consider below examples
1) isnull(t1.col1, convert(datetime, @var1)) <= convert(datetime, @var1)
2) isnull(t1.col2, '0') = '1'
3) isnull(t1.col3, 1) = isnull(@var2,1)
I was just thinking of rewriting these sort on clause in more simpler form which will enable optimizer to use the available indexes in place of scanning it.
1) ((t1.col1is not null and t1.col1 <= convert(datetime, @var1)) or t1.col1 is null)
2) (t1.col2 is not null and t1.col2 = '1')
3) ((t1.col3 is not null and @var3 is not null AND t1.col3 = @var3)
or
(t1.col3 is null AND @var3 = '1')
or
(t1.col3 is not null AND t1.col3 = '1' AND @var3 IS NULL)
or
(t1.col3 is null AND @var3 IS NULL))
I've tried to convert each of the statement not to use isnull by writing above clauses respectively.
And I just need you guys inputs to see if there is any better way to handle this sort of issues.
October 17, 2011 at 2:22 pm
1) isnull(t1.col1, convert(datetime, @var1)) <= convert(datetime, @var1)
-> t1.col1 <= convert(datetime, @var1) or t1.col1 is null
2) isnull(t1.col2, '0') = '1'
-> t1.col2 = '1'
3) isnull(t1.col3, 1) = isnull(@var2,1)
-> (t1.col3 = @var2) or
(t1.col3 is null and @var2 = 1) or
(@var2 is null and t1.col3 = 1) or
(t1.col3 is null abd @var2 is null)
I may be wrong, but I think this is how they break down. Would appreciate others comparing the options.
October 17, 2011 at 3:30 pm
Interesting exercise. What is the reasoning? Just for fun/learning? SARGability? If so, the ones Lynn posted look good for 2 and 3 but 1 still has a convert.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2011 at 3:36 pm
Sean Lange (10/17/2011)
Interesting exercise. What is the reasoning? Just for fun/learning? SARGability? If so, the ones Lynn posted look good for 2 and 3 but 1 still has a convert.
Look where the convert is on #1, it's on the input variable not the column being tested.
October 17, 2011 at 4:47 pm
I like Lynn's solutions. #1 moves the function to a scalar instead of a column that is indexed and should improve performance.
October 18, 2011 at 7:40 am
Lynn Pettis (10/17/2011)
Sean Lange (10/17/2011)
Interesting exercise. What is the reasoning? Just for fun/learning? SARGability? If so, the ones Lynn posted look good for 2 and 3 but 1 still has a convert.Look where the convert is on #1, it's on the input variable not the column being tested.
/facepalm
That's what I get for typing before reading all the details. :blush:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply