August 8, 2008 at 6:25 am
Given a 20 million row table of not overly dyanmic data, which is "better" given that 'stringfield is indexed and 'stringfield' is a VarChar(150) field that can be NULL and we want to filter the SELECT to find which rows have no data in this field. (either blank '' or NULL)
WHERE ... (stringfield='') OR (stringfield IS NULL)
or
WHERE ... ISNULL(stringfield, '') = ''
or
?
Any advise?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 8, 2008 at 7:11 am
first statement should be more efficient then 2nd one, because index does not participate or does not play role when any type of conversion is performed.
what was your outcomes by using these two different clauses?
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
August 8, 2008 at 7:13 am
The first one was a faster by about two seconds.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
August 8, 2008 at 7:18 am
My experience has been that the format of (column = '' or column is null) works better than the isnull(column, '') = '' method. I am not sure of the exact reason. I know that the isnull() method makes the where clause not sargable because it has to evaluate the function. Obviously the = '' is sargable, but I am not sure if the IS NULL condition is sargable.
Can you run each and see the difference in the exectuion plan or look at the reads being performed by the query?
Keep us posted on any results you get from testing.
August 8, 2008 at 7:50 am
Thanks for all the information. I was leaning towards the '= ' and you confirmed what i thought.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply