3

I have question related to isnull() function vs is null keyword in SQL Server.

I know that writing below condition in where clause of SQL statement will result in bad performance 

isnull(@C_FirstName, '') <> ''

We can replace same with 

 (@C_FirstName IS NOT NULL AND @C_FirstName <> '')

in the where clause

But will I get any performance gain in case of if condition?

Below is the example with two option

Option #1:

If `isnull(@C_FirstName, '') <> ''`

Option #2:

If `(@C_FirstName IS NOT NULL AND @C_FirstName <> '')`

From option 1 and option 2 which statement is recommended?

sufleR
  • 2,865
  • 17
  • 31
Sachin
  • 4,621
  • 6
  • 25
  • 31
  • 6
    Performance optimization comes after coding phase. Not during the coding phase – Shiplu Mokaddim Dec 04 '12 at 10:44
  • How about option 3 `if @C_FirstName <> ''`? – Mikael Eriksson Dec 04 '12 at 11:21
  • 1
    Unless you have [ANSI_NULLS](http://msdn.microsoft.com/en-us/library/ms188048.aspx) off. – Mikael Eriksson Dec 04 '12 at 11:21
  • Less performance optimisation is required after coding phase when good habits are followed during coding.... like avoiding wrapping columns in functions. – Nick.Mc Oct 29 '14 at 23:59
  • By 'bad performance' I think you mean non SARGable. This is only an issue if you actually have an index that is unable to be used. Wrapping a column in a function makes it non SARGable. Using the <> operator also makes it non SARGable. So I see no difference. I suggest you get your data in order and either make them all NULL or blanks, not both. – Nick.Mc Oct 30 '14 at 00:02

1 Answers1

2

ISNULL (or COALESCE) can impact performance when you are combining sets, such as with joins or when you are filtering data. Because you are applying manipulation to the attributes you're making a comparison to, the optimizer will not rely on index ordering. The database engine won't use existing indexes efficiently. Paraphrased from Page 52 - Querying Microsoft SQL Server 2012 - Training Kit

In our database systems, we are currently replacing similar code in our Views from something like this:

SELECT ISNULL(Salesman, '')
FROM Order

to this:

SELECT CASE WHEN IS NULL Salesman THEN '' ELSE Salesman END
FROM Order

It might seem a little counterintuitive because you're increasing the size of your code, but we've seen some modest performance gain when particular Views are leveraged extensively.

ITGuyOU
  • 29
  • 5