Tuesday 20 March 2012

What to use ISNULL() or COALESCE()

 

The NULL value always makes some problem to database developer. To handle the null value properly the SQL server provides two functions:

1.    ISNULL()

2.    COALESCE ()

 

Both functions act like same.

Before finding the difference between two functions we must understand, what is the functionality of both functions to take care of null values.

 

 

Suppose we have a Table names TBL-A

 

Roll

StudentName

StudentClass

1

JOYDEEP

1

2

TUHIN

1

3

NULL

1

 

 

To handle the NULL value with ISNULL()

 

SELECT ISNULL(StudentName,'NOT FOUND') StudentName

FROM   Table1-A

 

It returns "NOT FOUND" in studentname columns for roll=3

 

The syntax of the COALESCE is mentioned bellow

 

COALESCE ( expression [ ,...n ] )

 

 Unlike ISNULL, COALESCE takes multiple columns as expression. To understand it properly I give an example. Please study the example well and you will understand the COALESCE

 

 

CREATE TABLE dbo.wages

(

    emp_id        tinyint   identity,

    hourly_wage   decimal   NULL,

    salary        decimal   NULL,

    commission    decimal   NULL,

    num_sales     tinyint   NULL

);
GO

INSERT dbo.wages

    (hourly_wage, salary, commission, num_sales)

VALUES

    (10.00, NULL, NULL, NULL),

    (20.00, NULL, NULL, NULL),

    (30.00, NULL, NULL, NULL),

    (40.00, NULL, NULL, NULL),

    (NULL, 10000.00, NULL, NULL),

    (NULL, 20000.00, NULL, NULL),

    (NULL, 30000.00, NULL, NULL),

    (NULL, 40000.00, NULL, NULL),

    (NULL, NULL, 15000, 3),

    (NULL, NULL, 25000, 2),

    (NULL, NULL, 20000, 6),

    (NULL, NULL, 14000, 4);

GO

 

SELECT CAST(COALESCE(hourly_wage * 40 * 52,

   salary,

   commission * num_sales) AS money) AS 'Total Salary'

FROM dbo.wages

ORDER BY 'Total Salary';

 

The Output is mentioned bellow

 

Total Salary

10000.00

20000.00

20800.00

30000.00

40000.00

41600.00

45000.00

50000.00

56000.00

62400.00

83200.00

120000.00

 

 

Now what are the differences between them?

 

 

1.    COALESCE is ANSI-92 compliant and ISNULL is not. So if u migrate the SQL statement in any other RDBMS the COALESCE is helpful.
2.    COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order to compare three expressions with ISNULL, you would have to nest expressions:
SELECT ISNULL(ISNULL(Col1, Col2), Col3)

3.    In this SQL the ISNULL can provide some Error

 

      DECLARE @Field1 char(3),

               @Field2 char(50)

      SET @Field2 = 'Some Long String'

 

      SELECT ISNULL(@Field1, @Field2)

      --Returns 'Som'

      SELECT COALESCE(@Field1, @Field2)

--Returns 'Some Long String'

 

 

4.    Performance wise ISNULL and COALESCE are equal. It is found that the ISNULL is slightly better performer then COALESCE.

 

 

In my advice use COALESCES rather than ISNULL.

Hope you like this.

 

Posted by: MR. JOYDEEP DAS

 

6 comments:

  1. Thanks for the post, interesting stuff.
    Didn't notice until now the problem with the string truncation with ISNULL. Important stuff!
    Though when the scenario is right (only two values with same max length), it's probably better to use ISNULL for slightly better performance.

    ReplyDelete
  2. I believe coalesce is a much more powerful function but it doesn't flow very easily. When you are thinking of handling a null value what is closer to mind ISNULL or COALESCE which i have to think about how to spell every time I type it. Even when I'm working with Oracle, I encounter more people using DECODE or NVL to handle nulls.

    ReplyDelete
  3. Can someone demonstrate that IsNull performs better than Coalesce?

    I've tried it on data of 500,000+ rows and the performance is the same. But if I am missing something then I'd be interested to know what?

    The only time Coalesce will perform more slowly than IsNull is when the first non-null expression in its list of arguments has a data-type larger than the current data-type currently used for that column. In these instances it will re-size the column on the fly.

    But this is desirable behaviour, not a performance flaw. IsNull (as pointed out above) will return incorrect data. Which is not desirable behaviour.

    ReplyDelete
  4. Can someone demonstrate that IsNull performs better than Coalesce?
    No, I can't.
    I've run my test harness over 15 times. Each run tested each query type over 10 Mill executions. In each run I tested; empty test harness, IsNull & Coalesce, both with a Initial null value & also with an initial actual value. & it ran them in different combinations to avoid any pre-test cache issues.
    Also they were only executed in loops not reading from disk so IO delays were not relevant.

    The conclusion I reached was :-
    1. The results are so close that it is highly likely that SQL 2012 uses the same code to do both functions.
    NB: I did not check Perfmon to see if there was any difference in CPU load. Nor have I checked the source code or asked any of the SQL developers if this is true. (it is not that important)
    2. IN both ISNULL & COALESCE, if the 1st parameter is NULL then the function performs 8.8% faster than if the 1st parameter contains a non null value.
    3. Both functions execute thousands of times faster than one I/O. So this is never likely to be perf bottleneck discussion.

    In summary
    To me the choice to use one over the other is determined by code readability & not performance. My personal preference is :-
    a) ISNULL is used to strip out any potential NULLs from an input column (or expression) before they result in an inconvienient NULL in my output.
    b) COALESCE is handy to combine or switch between multiple input columns (or expressions) where logic dictates that only one option will be active at any one time. eg: i) the PayRoll example. ii) The EnglighName, GermanName, SpanishName example

    ReplyDelete