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:

Getting Help from Steve and the Stedman Solutions Team

We are ready to help. Steve and the team at Stedman Solutions are here to help with your SQL Server needs. Get help today by contacting Stedman Solutions through the free 30 minute consultation form.