Performance " IS Not Null" vs "Not IS Null"

Question

Viewing 2 posts - 1 through 1 (of 1 total)

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

xblondel SSC Eights! Points: 818 More actions · Answer 1

Hi!

Well, I performed the following tests (SQL2K SP3):

---

USE Northwind

GO

select ShipRegion from Orders

where ShipRegion IS NOT NULL

select ShipRegion from Orders

where NOT (ShipRegion IS NULL)

---

Auditing with the profiler gives (show plan statistics):

- first query:

Clustered Index Scan(OBJECT[Northwind].[dbo].[Orders].[PK_Orders]), WHERE[Orders].[ShipRegion]NULL))

- second query:

Clustered Index Scan(OBJECT[Northwind].[dbo].[Orders].[PK_Orders]), WHERE[Orders].[ShipRegion]NULL))

As you can see, SQL Server is doing the same thing in both cases, so it has no impact on performance whatsoever.

HTH,

Xavier