WHERE (ISNULL(columnName,0) > 0) vs WHERE (columnName IS NOT NULL) AND (columnName > 0)

Question

SELECT * FROM tableName WHERE (ISNULL(columnName,0) > 0)

OR

SELECT * FROM tableName WHERE (columnName IS NOT NULL) AND (columnName > 0)

which one is better?

columnName has int datatype

Answer 1

You don't need to check for null values in your query so I guess this would be better.

SELECT * FROM tableName WHERE columnName > 0

Answer 2

And why not just WHERE columnName > 0 ? The rest of the condition is seems redundant: Nulls will not be returned.

Answer 3

Better how?

I think the latter is more readable, others may disagree. You may have a house style that favours the former in which case there is no question.

As to performance, well I very much doubt either is causing a performance issue so you shouldn't optimise, premature optimisation is the root of all evil.

---

Here is a good question asking When is optimisation premature? and here is a definition of the term.

In this case the performance improves roughly in this order:

SELECT Count(*) FROM BigTable WHERE (ISNULL(SmallNumber,0) > 0) --140 ms
SELECT Count(*) FROM BigTable WHERE (SmallNumber IS NOT NULL) AND (SmallNumber > 0) --41 ms
SELECT Count(*) FROM BigTable WHERE SmallNumber > 0 --30 ms

But swap Count(*) with * and any tiny performance gain is lost in a sea for retrieving rows.

You can test it yourself after using this ugly lump of code to create a big table.

CREATE TABLE [dbo].[BigTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [BigNumber] [bigint] NULL, [SmallNumber] [int] NULL,
    CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED ( [ID] ASC ));
CREATE NONCLUSTERED INDEX [IX_BigTable] ON [dbo].[BigTable] ([SmallNumber] ASC);
With Digits as ( SELECT 0 AS d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
OneToAMillion as (SELECT (100000 * D6.d) + (10000 * D5.d) + (1000 * D4.d) + (100 * D3.d) + (10 * D2.d) + (1 * D1.d) AS Number
    FROM Digits D6, Digits D5, Digits D4, Digits D3, Digits D2, Digits D1)
INSERT INTO dbo.BigTable (BigNumber, SmallNumber) SELECT CAST(CHECKSUM(NEWID()) as BigInt) * CHECKSUM(NEWID()), CHECKSUM(NEWID()) FROM OneToAMillion;
UPDATE BigTable SET SmallNumber = Null WHERE BigNumber < 0;