WHERE (NOT (column_name IS NULL))

Question

WHERE (NOT (column_name IS NULL))

Express12

SSCoach

Points: 18514
More actions
April 20, 2012 at 10:15 am

#254613

Is their a best practice for re-coding this ? (this predicate is being applied to a 500 M row table - and performance stinks!)
SELECT stuff FROM table_xyz
WHERE (NOT (dbo.MyTablename.MyColumnName IS NULL))
BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Express12 (4/20/2012)
Is their a best practice for re-coding this ? (this predicate is being applied to a 500 M row table - and performance stinks!)
SELECT stuff FROM table_xyz
WHERE (NOT (dbo.MyTablename.MyColumnName IS NULL))

By putting the NOT in the where clause you are basically forcing a table scan. You posted this in a SQL Server 2008 forum, so I am wondering if a filtered index on the table using dbo.MyTablename.MyColumnName IS NOT NULL might not help some how? Sorry, I haven't had the opportunity to really work with filtered indexes yet.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

A recoding is not going to improve performance.

Perhaps an index on MyColumnName? It'll have to be covering to be of any use if more than about 1% of the table has a not null value for that column.

Can you post table definition, index definition and execution plan please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Lynn Pettis (4/20/2012)
By putting the NOT in the where clause you are basically forcing a table scan.

Not if there's a suitable index.

AdventureWorks (of some version or other)

CREATE INDEX idx_test2 ON Person.Person ([Title])

INCLUDE ([FirstName],[LastName])

SELECT Title, FirstName, LastName FROM Person.Person AS p WHERE NOT (title IS null)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

GilaMonster (4/20/2012)
Lynn Pettis (4/20/2012)
By putting the NOT in the where clause you are basically forcing a table scan.
Not if there's a suitable index.
AdventureWorks (of some version or other)
CREATE INDEX idx_test2 ON Person.Person ([Title])
INCLUDE ([FirstName],[LastName])
SELECT Title, FirstName, LastName FROM Person.Person AS p WHERE NOT (title IS null)

I guess it has just my experience with the data I have worked with. It seemed I got table or clustered index scans when I used NOT(somecolumn is null).

Could just be the data I had to work with.

Best thing to do is test, and test again.

Also, in this case, perhaps a look at the actual execution plan will help as well.