December 11, 2009 at 12:22 am
Just wondering,
I like to use ISNULL(column_name, 0) while my friend like to use COALESCE(column_name, 0).
We both try using ISNULL and COALESCE in our query and there is no different result.
In Books online, it's said that both behave differently. There's also the explanation, but I just don't
understand it completely :p
Is there any article regarding this topic?
And which one you usually use (ISNULL or COALESCE) and why you use it?
For now, I'll keep my habit using ISNULL, so will my friend keep using COALESCE 😀
Thanx for any explanation, link, or you opinion 🙂
December 11, 2009 at 2:21 am
Prior to SQL 2008 ISNULL was considerably faster than COALESCE. This makes sense as the code behind ISNULL has to deal with a fixed number of input variables (2) and COALESCE has to be designed to work with any number of variables, so this will invariably involve extra computations.
In SQL 2008, I've seen a thread where people say that the performance is now for all intent and purpose the same for both (I wonder if the optimiser just converts it to ISNULL if there are only 2 variables).
Personally, if I'm only working with two values, then I'll use ISNULL and if I need more I'll use COALESCE in the spirit in which the two functions were designed.
December 11, 2009 at 7:12 am
Howard has provided a pretty good explanation. Use ISNULL when you only have 2 option, COALESCE with more than 2. They also handle data types and lengths differently. Try this:
SELECT
ISNULL(Nullif('abc', 'abc'), '123456') AS using_isnull,
COALESCE(Nullif('abc', 'abc'), '123456') AS using_coalesce,
ISNULL(Nullif('abc', 'abc'), 123456) AS int_using_isnull,
COALESCE(Nullif('abc', 'abc'), 123456) AS int_using_coalesce
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 11, 2009 at 7:43 am
Since I prefer as much consistency as possible, I try to stick to Coalesce. There are times when I can't use IsNull and have to use Coalesce, but there aren't times when I have to use IsNull and can't use Coalesce. So, I try to use Coalesce.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2009 at 9:07 pm
Just to expand on Jack's point a bit, because it is an important one:
COALESCE( expression [ ,...n ] ) returns the data type of the expression with the highest data type precedence.
ISNULL(check_expression, replacement_value) returns the same type as check_expression.
So:
SELECT ISNULL(CAST(NULL AS INT), 5.5) -- Returns 5
SELECT COALESCE(CAST(NULL AS INT), 5.5) -- Returns 5.5
SELECT DATALENGTH(ISNULL(CAST(NULL AS VARCHAR(5)), N'Hello')) -- Returns 5
SELECT DATALENGTH(COALESCE(CAST(NULL AS VARCHAR(5)), N'Hello')) -- Returns 10
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 14, 2009 at 8:26 am
actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.
coalesce(custname, last, first) = isnull( isnull( custname, last ), first )
Therefore a coalesce is basically a nested isnull.
December 14, 2009 at 8:29 am
BaldingLoopMan (12/14/2009)
actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.coalesce(custname, last, first) = isnull( isnull( custname, last ), first )
Therefore a coalesce is basically a nested isnull.
True. Would be a pain to type out, be essentially unreadable, and I'd hate to have to maintain something that nested 5 or 10 deep, but it could be done.
I'll still stick with Coalesce.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2009 at 8:30 am
To each their own.
December 14, 2009 at 8:37 am
BaldingLoopMan (12/14/2009)
actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.coalesce(custname, last, first) = isnull( isnull( custname, last ), first )
Therefore a coalesce is basically a nested isnull.
That is not totally accurate as noted by the posts by myself and Paul White where you can see the IsNull and Coalesce functions are different. If you run this query:
DECLARE @test-2 TABLE (colA CHAR(3))
INSERT INTO @test-2 (
colA
)
SELECT
'abc'
UNION ALL
SELECT
NULL
SELECT
ISNULL(colA, '123456') AS using_isnull,
COALESCE(colA, '123456') AS using_coalesce
FROM
and look at the exection plan you will see that SQL Server keeps the IsNull function for ISNULL in Compute Scalar operator, but converts the COALESCE column to a CASE statement.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 14, 2009 at 8:58 am
Yea. i didn't follow what u guys were saying before.
I see what your saying now. Wow. That's crazy and good to know.
December 14, 2009 at 9:02 am
BaldingLoopMan (12/14/2009)
To each their own.
Even in cases where the data type precedence won't cause a problem, I find the Coalesce version below much easier to read, understand, and maintain, than the second:
if object_id(N'tempdb..#People') is not null
drop table #People;
create table #People (
ID int identity primary key,
NamePrefix varchar(100),
NameFirst varchar(100),
NameMiddle varchar(100),
NameLast varchar(100),
NameSuffix varchar(100));
insert into #People (NamePrefix, NameFirst, NameMiddle, NameLast, NameSuffix)
select null, 'Adam', null, 'Abrahms', null union all
select '', 'Bob', null, 'Birch', null union all
select 'Dr', 'Carl', 'C', 'Carlson', 'PhD' union all
select 'Rev', null, null, 'Dodgy', 'Jr';
;with Cleanup (Prefix, First, Middle, Last, Suffix) as
(select IsNull(NullIf(NamePrefix, ''), 'Mr/Ms'), NullIf(NameFirst, ''), NullIf(NameMiddle, ''),
NullIf(NameLast, ''), NullIf(NameSuffix, '')
from #People)
select
coalesce(
Prefix + ' ' + First + ' ' + Middle + ' ' + Last + ', ' + Suffix,
Prefix + ' ' + First + ' ' + Middle + ' ' + Last,
Prefix + ' ' + First + ' ' + Last + ', ' + Suffix,
Prefix + ' ' + First + ' ' + Last,
Prefix + ' ' + Last + ', ' + Suffix,
Prefix + ' ' + Last)
from Cleanup;
;with Cleanup (Prefix, First, Middle, Last, Suffix) as
(select IsNull(NullIf(NamePrefix, ''), 'Mr/Ms'), NullIf(NameFirst, ''), NullIf(NameMiddle, ''),
NullIf(NameLast, ''), NullIf(NameSuffix, '')
from #People)
select
IsNull(Prefix + ' ' + First + ' ' + Middle + ' ' + Last + ', ' + Suffix,
IsNull(Prefix + ' ' + First + ' ' + Middle + ' ' + Last,
IsNull(Prefix + ' ' + First + ' ' + Last + ', ' + Suffix,
IsNull(Prefix + ' ' + First + ' ' + Last,
IsNull(Prefix + ' ' + Last + ', ' + Suffix,
Prefix + ' ' + Last)))))
from Cleanup;
Yes, they produce the same results, but if you want to add another combination and precedence to the first one, it's easier than the second one.
(This is an actual function I had to build for processing names for mailing lists. It's a real-world example.)
Even if you change the layout of the second so that the precedences line up nicely like the first one:
;with Cleanup (Prefix, First, Middle, Last, Suffix) as
(select IsNull(NullIf(NamePrefix, ''), 'Mr/Ms'), NullIf(NameFirst, ''), NullIf(NameMiddle, ''),
NullIf(NameLast, ''), NullIf(NameSuffix, '')
from #People)
select
IsNull(Prefix + ' ' + First + ' ' + Middle + ' ' + Last + ', ' + Suffix,
IsNull(Prefix + ' ' + First + ' ' + Middle + ' ' + Last,
IsNull(Prefix + ' ' + First + ' ' + Last + ', ' + Suffix,
IsNull(Prefix + ' ' + First + ' ' + Last,
IsNull(Prefix + ' ' + Last + ', ' + Suffix,
Prefix + ' ' + Last)))))
from Cleanup;
It's still not as obvious what needs to be done to add a new combination in the middle of the list.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2009 at 9:27 am
yea. i'm still a little freaked out that the isnull returns the first params type and length no mater what. Don't know how i didnt run into this before. Run the below. Crazy,
declare @colA varCHAR(3)
declare @colb varCHAR(10)
set @colA = 'abc'
set @colb = '123456789'
select 'when @colA is not null', ISNULL(@colA,@colB) AS using_isnull,
COALESCE(@colA,@colB) AS using_coalesce
set @colA = null
set @colb = '123456789'
select 'They should both be equal'
, case when ISNULL(@colA,@colB) = @colb then 'They Equal' else 'They Not Equal using isnull' end
, case when coalesce(@colA,@colB) = @colb then 'They Equal using coalesce' else 'They Not Equal' end
December 15, 2009 at 8:11 am
Trying to think back where this wouldf have burned me in the past.
declare @colA CHAR(3)
declare @colb CHAR(10)
set @colA = null
set @colb = '123456789'
select 'when @colA is null', case when ISNULL(@colA,@colB) = @colB then 'Their equal' else 'Their not equal, ISNULL(@colA,@colB) <> @colB' end AS using_isnull,
case when COALESCE(@colA,@colB) = @colB then 'Their equal, COALESCE(@colA,@colB) = @colB' else '' end AS using_coalesce
this would be a scenerio i can forsee getting burnt.
December 15, 2009 at 8:46 am
One reason to use COALESCE rather than IsNull... COALESCE is in the ANSI standards, IsNull is a Microsoft extension to the standard.
February 6, 2010 at 5:43 pm
The only time I would use isNull is where I have a very simple case, I would never try to do anything at all complicated with isNull (like nesting it). I guess this is probably because isNull is (a) not portable and (b) not pretty if you have to modify something that uses it in a complicated manner. I do use it in simple cases (for example where producing ad hoc diagnostic text to log) despite the non-portability, because it's 2 fewer characters to type than coalesce and I'm extremely lazy.
Tom
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply