Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate COALESCE (ANSI 92 SQL standard) and ISNULL (T-SQL only) usage and performance implications.

-- SQL coalesce: returns the first non-null expression among its arguments
SELECT COALESCE(NULL, NULL, NULL, NULL, 'New York', NULL, NULL, 'Seattle', NULL)
-- New York
 
-- SQL isnull: two arguments only, replaces NULL with the specified replacement value
SELECT ISNULL(NULL, ISNULL(NULL, ISNULL(NULL, ISNULL('New York', 'New York'))))
-- New York
 
SELECT COALESCE( NULL, 'Missing Data')
SELECT  ISNULL ( NULL, 'Missing Data')
-- Missing Data
------------
 
-- SQL coalesce to select first non-null year sales for sales staff
SELECT [FullName]
      ,FirstYearPerformance = COALESCE([2002], [2003], [2004])
      ,[SalesTerritory]
      ,[2002]
      ,[2003]
      ,[2004]
  FROM [AdventureWorks].[Sales].[vSalesPersonSalesByFiscalYears]
  ORDER BY FirstYearPerformance desc
GO

-- Results

 
 
-- Equivalent CASE expression
-- COALESCE syntax is simpler
SELECT   [FullName],
         FirstYearPerformance = CASE
                                  WHEN [2002] IS NOT NULL THEN [2002]
                                  WHEN [2003] IS NOT NULL THEN [2003]
                                  WHEN [2004] IS NOT NULL THEN [2004]
                                  ELSE NULL
                                END,
         [SalesTerritory],
         [2002],
         [2003],
         [2004]
FROM     [AdventureWorks].[Sales].[vSalesPersonSalesByFiscalYears]
ORDER BY FirstYearPerformance DESC

GO
-- SQL isnull version - nested isnulls - isnull nesting
-- SQL coalesce syntax simpler than isnull syntax - same result
SELECT [FullName]
      ,FirstYearPerformance = ISNULL([2002], ISNULL([2003], ISNULL([2004],[2004])))
      ,[SalesTerritory]
      ,[2002]
      ,[2003]
      ,[2004]
  FROM [AdventureWorks].[Sales].[vSalesPersonSalesByFiscalYears]
  ORDER BY FirstYearPerformance desc
GO
------------
 
-- Kill all connection in the database - DBA utility - Dynamic SQL
-- May be used by DBA to get exclusive access to the database
DECLARE  @SQL NVARCHAR(MAX)
SELECT @SQL = ISNULL(@SQL,'') + 'Kill ' + CAST(spid AS VARCHAR) + '; '
FROM   sys.sysprocesses
WHERE  DBID = DB_ID('AdventureWorks')
       AND spid > 50
PRINT @SQL
-- Kill 52; Kill 54; Kill 55; Kill 57; Kill 58; Kill 59; Kill 60; Kill 61;
-- EXEC sp_executeSQL @SQL -- remove comments for production run
------------
 
 
-- SQL make comma-limited list
DECLARE  @DeptList VARCHAR(MAX);
SELECT   @DeptList = coalesce(@DeptList + ', ','') + Name
FROM     AdventureWorks.HumanResources.Department
ORDER BY Name
 
SELECT @DeptList
GO
/*  Result
Document Control, Engineering, Executive, Facilities and Maintenance,
Finance, Human Resources, Information Services, Marketing, Production,
Production Control, Purchasing, Quality Assurance, Research and Development,
Sales, Shipping and Receiving, Tool Design
*/
-- Equivalent with ISNULL
DECLARE  @DeptList VARCHAR(MAX);
 
SELECT   @DeptList = isnull(@DeptList + ', ','') + Name
FROM     AdventureWorks.HumanResources.Department
ORDER BY Name
 
SELECT @DeptList
GO
------------
 
-- T-SQL build comma-limited list
DECLARE  @ColorList VARCHAR(MAX);
-- SQL table variable
DECLARE @Colors TABLE (Color varchar(32))
INSERT @Colors SELECT DISTINCT Color
FROM     AdventureWorks.Production.Product 
SELECT * FROM @Colors ORDER BY Color desc
/*
Color
NULL
Black
Blue
Grey
Multi
Red
Silver
Silver/Black
White
Yellow
*/
-- Equivalent result for coalesce and isnull
SELECT   @ColorList = COALESCE(@ColorList + ', ','') +  Color
-- SELECT   @ColorList = ISNULL(@ColorList + ', ','') +  Color
FROM     @Colors
ORDER BY Color
 
SELECT @ColorList
GO
/*
Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow
*/
------------
 
-- SQL replace NULL column value - coalesce, isnull
-- SQL replace NULL string column with empty space
SELECT FirstName + ' ' + COALESCE(MiddleName,'') + ' ' + LastName AS 'FullName',
       FirstName,
       MiddleName,
       LastName,
       ContactID
FROM   AdventureWorks.Person.Contact
GO
/* Partial results
 
FullName                FirstName   MiddleName  LastName    ContactID
Gustavo  Achong         Gustavo     NULL        Achong      1
Catherine R. Abel       Catherine   R.          Abel        2
Kim  Abercrombie        Kim         NULL        Abercrombie 3
Humberto  Acevedo       Humberto    NULL        Acevedo     4
*/
-- Same result with ISNULL
 
SELECT FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName AS 'FullName',
       FirstName,
       MiddleName,
       LastName,
       ContactID
FROM   AdventureWorks.Person.Contact
GO
------------

 
------------
-- SQL COALESCE vs ISNULL in Performance
------------
-- In some cases ISNULL performs better than COALESCE
-- Database engine generates different execution plans
-- SET SHOWPLAN_TEXT ON 
-- The above can be used to see the execution plan in text format
SET STATISTICS  IO  ON
GO
 
-- COALESCE version
DBCC DROPCLEANBUFFERS
DECLARE  @SOList VARCHAR(MAX);
SET @SOList = NULL;
SELECT   TOP ( 60000 ) @SOList = COALESCE(@SOList + ', ','') +
                                 CAST(SalesOrderID AS VARCHAR)
FROM     AdventureWorks.Sales.SalesOrderDetail
ORDER BY SalesOrderID
SELECT LEFT(@SOList,60)
GO
-- 25 sec execution time
-- 43659, 43659, 43659, 43659, 43659, 43659, 43659, 43659, 4365
/* Messages
Table 'SalesOrderDetail'. Scan count 1, logical reads 657, physical reads 3, 
read-ahead reads 1242, lob logical reads 0, lob physical reads 0, 
lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 24981, physical reads 0, 
read-ahead reads 0, lob logical reads 4539236, lob physical reads 0, 
lob read-ahead reads 0.
 
 
*/
-- ISNULL version
DBCC DROPCLEANBUFFERS
DECLARE  @SOList VARCHAR(MAX);
SET @SOList = NULL;
SELECT   TOP ( 60000 ) @SOList = ISNULL(@SOList + ', ','') +
                                 CAST(SalesOrderID AS VARCHAR)
FROM     AdventureWorks.Sales.SalesOrderDetail
ORDER BY SalesOrderID
SELECT LEFT(@SOList,60)
GO
-- 5 sec execution time
/* Messages
 
Table 'SalesOrderDetail'. Scan count 1, logical reads 657, physical reads 2, 
read-ahead reads 1242, lob logical reads 0, lob physical reads 0, 
lob read-ahead reads 0.
 
*/
SET STATISTICS  IO  OFF

GO
-- Execution plan for COALESCE version
SET SHOWPLAN_TEXT ON;
GO
DECLARE @SOList VARCHAR(MAX);
SET @SOList = NULL; 
SELECT TOP (60000) @SOList = COALESCE(@SOList + ', ','') +
                             CAST(SalesOrderID AS VARCHAR)
FROM AdventureWorks.Sales.SalesOrderDetail
ORDER BY SalesOrderID
SELECT LEFT(@SOList,60)
GO
SET SHOWPLAN_TEXT OFF;
/*
StmtText
  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN ([@SOList]+', ') IS NOT NULL THEN [@SOList]+', ' ELSE CONVERT_IMPLICIT(varchar(max),'',0) END+[Expr1004]))
       |--Top(TOP EXPRESSION:((60000)))
            |--Compute Scalar(DEFINE:([Expr1004]=CONVERT(varchar(30),[AdventureWorks].[Sales].[SalesOrderDetail].[SalesOrderID],0)))
                 |--Clustered Index Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]), ORDERED FORWARD)

*/
-- Execution plan for ISNULL version
SET SHOWPLAN_TEXT ON;
GO
DECLARE @SOList VARCHAR(MAX);
SET @SOList = NULL; 
SELECT TOP (60000) @SOList = ISNULL(@SOList + ', ','') +
                             CAST(SalesOrderID AS VARCHAR)
FROM AdventureWorks.Sales.SalesOrderDetail
ORDER BY SalesOrderID
SELECT LEFT(@SOList,60)
GO
/*
  |--Compute Scalar(DEFINE:([Expr1003]=isnull([@SOList]+', ',CONVERT_IMPLICIT(varchar(max),'',0))+[Expr1005]))
       |--Top(TOP EXPRESSION:((60000)))
            |--Compute Scalar(DEFINE:([Expr1005]=CONVERT(varchar(30),[AdventureWorks].[Sales].[SalesOrderDetail].[SalesOrderID],0)))

                 |--Clustered Index Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]), ORDERED FORWARD)
*/

------------

Related articles:

Adam Machanic: Performance: ISNULL vs. COALESCE

Creative uses for COALESCE() in SQL Server

COALESCE (Transact-SQL)