ISNULL(X,Y) or ISNULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ISNULL(X,Y) or ISNULL

Author

Topic

Robowski
Posting Yak Master

101 Posts

Posted - 2014-02-18 : 07:05:27

Just wondering if what would be best for performance.

Gender field in this case will only hold 2 values, Female and Male and in some cases a NULL mark.

If you are creating a dataset only to return people who are female or have a null gender would it be best for performance to use:

Ignore the 1=1, for development use only and wont be used in production code.

Gender column isn't indexed

WHERE 1=1
AND ISNULL(Gender, 'Female') = 'Female'

or

WHERE 1=1
AND (Gender = 'Female' OR Gender IS NULL)

Also, what would the best way of testing this be? just looking at the execution plan?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-18 : 11:01:54

I'm glad you asked out to test this yourself - that is a good sign :)

I usually compare the execution plans (if there's no difference then it doesn't matter which you use - for that statement).
And I also compare output of the statement with these SETs:

set statistics io on --physical and logical reads
set statistics time on --execution time by statement in milliseconds

Let us know what you find

Be One with the Optimizer
TG

Robowski
Posting Yak Master

101 Posts

Posted - 2014-02-18 : 11:33:33

quote:
Originally posted by TG

I'm glad you asked out to test this yourself - that is a good sign :)

I usually compare the execution plans (if there's no difference then it doesn't matter which you use - for that statement).
And I also compare output of the statement with these SETs:

set statistics io on --physical and logical reads
set statistics time on --execution time by statement in milliseconds

Let us know what you find

Be One with the Optimizer
TG

Cheers,

usually use thing liks showplan xml, and the execution plans but was wondering if there were any further ways of doing. Can be a bit tricky when dealing with nearly a billion rows! also cautious of the fact the first execution plan may be cached and the following partially using that and five a slightly false performance boost.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources