LIKE ''%'' vs IS NOT NULL (more bad code?)
-
Is there any performance gain to using LIKE '%' rather than IS NOT NULL?
Any other reason why one would choose the former over the latter (other than driving folks like me nuts
...)? -
I doubt there's any performance difference. I expect someone who didn't know SQL tried to use " <> NULL", found it didn't work, and rather than learning about NULLs, found a workaround.
[Edit] Actually, in testing, it appears that "LIKE '%'" should perform worse. Try the code below - even with a non-unique index and large percentage of Nulls in the entire set, using IS NOT NULL will generate an index seek, which should be faster:
Select *
Into #t
From
(
Select 1 As Id Union All
Select 2 Union All
Select 3 Union All
Select 4 Union All
Select 5 Union All
Select 6 Union All
Select 7 Union All
Select 8 Union All
Select 9 Union All
Select Null Union All
Select Null Union All
Select Null Union All
Select Null Union All
Select Null Union All
Select Null Union All
Select Null
) dt
Create index #ixt on #t (Id)
Select * from #t
Where Id Like '%'
Select * from #t
Where Id Is Not Null
-
Thanks for the feedback. Sometimes I just need a sanity check.
-
You do get a slight perf gain from id > 0 (or some number assuming there is a fixed domain -- like identities are usually 1 or higher) but that is only with a good number of records.
-
Considering we're talking about a VLDB that processes terabytes of data daily, even a "slight" improvement is a Good Thing.
-
might be worth trying
>=
''
then.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply