27

I am using ISNULL in MS SQl server 2008, since my table is too huge, whether using of ISNULL may cause any thing to the performance ?.

Thanks in advance

kbvishnu
  • 14,760
  • 19
  • 71
  • 101
  • 1
    Depends on a lot of things, as ever. How you use it, where you use it, what your query is. More detail might help. – Paddy Jan 19 '12 at 11:03
  • @SergioTulentsev I am working on an enhancement project. So we have added some new colomns to tbls. That will be Null. The data which is available for development is only few. The production Database is very huge. I cannot access to it. – kbvishnu Jan 19 '12 at 11:12
  • @Paddy My main doubt is if we are using ISNULL(col,0) in some columns we added for enhancements (c above comment pls), we can avoid NULL values by using ISNULL at SP or need to check NULL In code. What is better ? – kbvishnu Jan 19 '12 at 11:14
  • Having ISNULL in the SELECT list is not measurable compared to the rest of the query. Don't [optimise prematurely](http://en.wikipedia.org/wiki/Program_optimization#When_to_optimize) – gbn Jan 19 '12 at 11:15
  • 1
    @Harie - if you need to load up your DEV database, you might want to take a look at something like this: http://www.red-gate.com/products/sql-development/sql-data-generator/ – Paddy Jan 19 '12 at 11:36

8 Answers8

66

ISNULL() in the select-clause has neglible influence on the performance. In the where-clause on the other hand it can have a very huge impact on performance, since it prevents the optimizer for using an index on that column.

where isnull(col1, 0) = 0 -- unable to use index, because every 
                          -- row has to be evaluated

where col1 = isnull(@myVar, 0) -- index will be used, since isnull(@myVar, 0) 
                               -- returns the same static value for every row and 
                               -- not every row has to be evaluated by the function.

So, when using isnull() in a where-clause, evaluate if it prevents the query optimizer from using an index. If so, consider creating a computed column with the result if isnull(col1, 0) and index the computed column and use it in your where-clause.

Heino Zunzer
  • 771
  • 1
  • 4
  • 4
16

Yes it can. For optimizer is better rewrite the query (if possible) to form

(Field = @x OR @x IS NULL)

Because using functions in certain cases prevents from optimizer to use statistics and sometimes forced implicit datatype conversions

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
15

If you need to use it, then any differences between ISNULL and alternatives like COALESCE or CASE are minuscule. Don't worry about it

Any differences come from how datatypes are handled. COALESCE/CASE can add implicit data type conversions whereas ISNULL has simpler rules.

Edit

ISNULL in the SELECT list to suppress NULLS is trivial. The main work will done in processing rows and data. An extra ISNULL won't be measurable: Don't optimise prematurely

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 2
    As per another answer on this post, the ISNULL function has a significantly different performance profile when used in a SELECT vs when used in a WHERE or JOIN clause. My experience confirms this to be the case. – David Alpert Feb 19 '17 at 17:55
  • @DavidAlpert Agreed. It is easy to misuse – gbn Feb 24 '17 at 08:10
1

Avoid using isNull in where clause. Refer This article.

user2501716
  • 19
  • 1
  • Hey there--this question already has an accepted answer. This is good info, but would be better as a comment :) – landons Jun 24 '13 at 22:02
  • I don't get that article. It is looking at the naked column already. Isnull(@someVar, 0) should not be expensive. Isnull(someColumn, 0) could be expensive if that makes the index not be used. – Brian White Jan 05 '15 at 19:23
1

Yes there is a performance issue afaik, in SQL Server Studio 2012.

The problem is quite glaring when I used ISNULL in combination with OVER. After optimizing (i.e. putting ISNULL in the sub-query I am using OVER on) run time reduced from (estimated) 25.2 hours down to 102 seconds.

My guess is ISNULL is OK when you run it over an entire column (for example, in a plain-ol' SELECT). But when you run it with OVER it is called anew every time, thus dragging down performance.

Not ready to drill down further. Simply putting it here for others' reference.

Teng L
  • 297
  • 3
  • 17
1

Watch out if using ISNULL with a sub query as the replacement_value.

Seems like it runs the sub query even if the check_expression value is not NULL which doesn't help performance.

CASE performs as expected.

Steve A
  • 11
  • 1
0

As it has been already mentioned it depends on how and where you are using it in your query. May be you might want to show the way you are using it in your query.

Also I would recommend you to go over this - What makes a SQL statement sargable?

Community
  • 1
  • 1
vmvadivel
  • 1,041
  • 5
  • 7
0

It depends on how you are useing it, but you can build execution plans in the both cases (with ISNULL() and without it) and compare the results.

ceth
  • 44,198
  • 62
  • 180
  • 289