Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

ISNULL vs COALESCE w.r.t to Computed columns

Oct 5 2011 5:01AM by Manoj   

The difference between ISNULL & COALESCE are well known among people.One difference I came to know when I was referring to MSDN & that was while creating computed columns.

  • With ISNULL() we can create both persisted & non-persisted computed columns.
  • But with COALESCE() we can not created non-persisted computed column, but only persisted column.

As per MS BOL (MSDN), ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. Thus to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute.

Read More..   [20 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Manoj
243 · 1% · 185
8
 
0
Knew
 
10
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

2  Comments  

  • Oh, very well done. This is actually some very important information because, in the name of portability (and true portability is still a myth if you do anything really practical), a lot of people lean toward using COALESCE for two part implicit ISNULLs without understanding the differences.

    There are a couple of other differences that people should be made aware of, as well...

    1. ISNULL will always try to implicitly convert the datatype of the 2nd operand to the datatype of the first while COALESCE will return the highest precedence of all the datatypes in all of the operands of expression. That's super important if you're using COALESCE in the process of building a Temp Table on-the-fly with SELECT/INTO because it could change the datatype of the resulting column in the Temp Table to something you weren't expecting and query predicates against that column may cause implicit conversions making it impossible to do index seeks. The same holds true for computed columns and it can really make a tuning mess unless you're careful to use the correct data type in all the operands of expression.

    2. Behind the scenes, COALESCE is more complex at the machine language level and, when used across hundreds of thousands of rows, is actually a tiny bit slower than ISNULL.

    commented on Oct 5 2011 6:09AM
    Jeff Moden
    160 · 1% · 298
  • True as Jeff has pointed out ISNULL would take the datatype and length of the first param - http://vadivel.blogspot.com/2011/09/isnull-would-take-datatype-and-length.html

    commented on Oct 6 2011 7:42AM
    Vadivel
    475 · 0% · 79

Your Comment


Sign Up or Login to post a comment.

"ISNULL vs COALESCE w.r.t to Computed columns" rated 5 out of 5 by 8 readers
ISNULL vs COALESCE w.r.t to Computed columns , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]