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