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
More from Stedman Solutions:
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