Using the TSQL COALESCE Function

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:

Additional Training on my YouTube Channel
Free SQL Query Training

Using the TSQL COALESCE Function

Leave a Reply Cancel reply