Pages

Saturday, June 1, 2013

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
FullNameFirstYearPerformanceSalesTerritory200220032004
Jae B Pak5287044.313United KingdomNULL52870445015682
Jillian  Carson3308895.851Central330889649918683857164
Tsvi Michael Reiter3242697.013Southeast324269726611562811013
Linda C Mitchell2800029.154Southwest280002946472255200475
José Edvaldo Saraiva2532500.913Canada253250114887933189356
Rachel B Valdez2241204.042GermanyNULLNULL2241204
Shu K Ito2040118.623Southwest204011928703213018725
Michael G Blythe1951086.826Northeast195108747439074557045
Lynn N Tsoflias1758385.926AustraliaNULLNULL1758386
Ranjit R Varkey Chudukatil1677652.437FranceNULL16776523827950
Pamela O Ansman-Wolfe1473076.914Northwest1473077900368.61656493
David R Campbell1243580.769Northwest124358113774311930886
Garrett R Vargas1135639.263Canada113563914801361764939
Tete A Mensa-Annan883338.7107NorthwestNULL883338.71931620
 

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

No comments:

Post a Comment