Ask - What is best method (Performance gain) to use in where condition IsNULL function or IS NULL with value check

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Ask

Ask questions, discuss or help others by answering


	Ask a question


	Subscribe..

What is best method (Performance gain) to use in where condition IsNULL function or IS NULL with value check

Mar 20 2011 12:00AM by Samir Matkar

Hi SQL Expert,

In my table having a column as IsDuplicate of bit field which currently contains the value NULL or 1. Current table size is growing each day by around 30000 rows. The column getting updated as "1" if it found any duplicates rows .

My Select Query should get the rows where IsDuplicate column don't have value "1" . The following are 2 SQL which i think to find the Rows where IsDuplicate is not 1

which is best SQLto apply for performance gain. ?

Select * from mytable where IsNull(IsDuplicate,0) = 0 OR
Select * from mytable where( IsDuplicate is null or IsDuplicate =0 )

*Note- In future some new developer might update the column IsDuplicate =0 About index , The colunn don't have index currently but yeah we will include in it

Could any body Help which is the best SQL to Apply or has better solution

Thanks in Advance

Submitted under: Microsoft SQL Server · TSQL · · ·

Previous: File Upload Web Service
Next: XML Formate

Samir Matkar

242 · 1% · 186

4 Replies

What about Select * from mytable where IsDuplicate<>1?

commented on Mar 21 2011 9:18AM

Madhivanan

3 · 39% · 12472

I suggest to not use SELECT * but rather specify each field. Otherwise I agree with Madhivanan as <> 1 should be the best solution.

commented on Mar 21 2011 9:20AM

Naomi

33 · 6% · 1774

Naomi & Madhivanan,

The below will NOT work as expected by the user. Note that you can't compare any value with a NULL. Any comparison with UNKNOWN is always UNKNOWN.

Select * from mytable where IsDuplicate<>1

Samir,

Your best bet is to use #2 i.e

Select * from mytable where( IsDuplicate is null or IsDuplicate =0 )

--Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

commented on Apr 1 2011 4:34PM

Sankar

113 · 1% · 454

I would recommend you use a default value (which I guess should be 0 in this case) instead of allowing NULL. The existing data can be updated to the default value manually. This will help to get rid of the OR in the WHERE clause which usually is a performance killer.

commented on Aug 15 2012 5:57AM

Jacob Sebastian

1 · 100% · 32004

Your Reply

Sign Up or Login to post a comment.