Using the TSQL COALESCE Function

Download PDF

Here is a short video tutorial that shows how to use the COALESCE function in T-SQL on SQL Server.

This was originally part of my free SQL query training for the 70-461 certification exam.

Here is the first sample code from the video:

-- COALESCE

USE [QueryTraining];

CREATE TABLE [dbo].[Departments](
	[ID] [INTEGER] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[Department] [VARCHAR](200) NOT NULL,
	[Details] [VARCHAR](4000) NULL,
	[Parent] [INT] NULL,
	[Archived] [BIT] NOT NULL DEFAULT(0)
);

INSERT INTO [dbo].[Departments]
           ([Department])
     VALUES
           ('Camping'),
           ('Cycle'),
           ('Snowsports'),
           ('Fitness');

DECLARE @campingDepartmentId as INT;
SELECT @campingDepartmentId = ID
  FROM [dbo].[Departments]
  WHERE [Department] = 'Camping';

INSERT INTO [dbo].[Departments]
           ([Department], [Parent])
     VALUES
           ('Tents', @campingDepartmentId),
           ('Backpacks', @campingDepartmentId),
           ('Sleeping Bags', @campingDepartmentId),
           ('Cooking', @campingDepartmentId);

-- examine the table
SELECT [ID],
	   [Department],
       [Details],
       [Parent],
       [Archived]
  FROM [dbo].[Departments];

-- clean up some NULLs with COALESCE
SELECT [ID],
	   [Department],
       [Details],
       COALESCE([Parent], 0) as Parent,
       [Archived]
  FROM [dbo].[Departments];

And then the code that shows the performance differences:

-- COALESCE compared to ISNULL
-- turn on actual execution plan CTRL+M
set statistics io on;
SELECT [ID],
	   [Department],
       [Details],
	   [Parent],
       COALESCE((SELECT MAX(Parent) FROM [dbo].[Departments]), 0) as MaxParent,
       [Archived]
  FROM [dbo].[Departments];

SELECT [ID],
	   [Department],
       [Details],
	   [Parent],
       ISNULL((SELECT MAX(Parent) FROM [dbo].[Departments]), 0) as MaxParent,
       [Archived]
  FROM [dbo].[Departments];

More Info:

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

Leave a Reply

Your email address will not be published. Required fields are marked *

*