datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to use COALESCE and ISNULL?

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

FullName FirstYearPerformance SalesTerritory 2002 2003 2004
Jae B Pak 5287044.313 United Kingdom NULL 5287044 5015682
Jillian  Carson 3308895.851 Central 3308896 4991868 3857164
Tsvi Michael Reiter 3242697.013 Southeast 3242697 2661156 2811013
Linda C Mitchell 2800029.154 Southwest 2800029 4647225 5200475
José Edvaldo Saraiva 2532500.913 Canada 2532501 1488793 3189356
Rachel B Valdez 2241204.042 Germany NULL NULL 2241204
Shu K Ito 2040118.623 Southwest 2040119 2870321 3018725
Michael G Blythe 1951086.826 Northeast 1951087 4743907 4557045
Lynn N Tsoflias 1758385.926 Australia NULL NULL 1758386
Ranjit R Varkey Chudukatil 1677652.437 France NULL 1677652 3827950
Pamela O Ansman-Wolfe 1473076.914 Northwest 1473077 900368.6 1656493
David R Campbell 1243580.769 Northwest 1243581 1377431 1930886
Garrett R Vargas 1135639.263 Canada 1135639 1480136 1764939
Tete A Mensa-Annan 883338.7107 Northwest NULL 883338.7 1931620

 
 
-- 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)

 

Exam Prep 70-461
Exam 70-461