Choose() vs Case statements in SQL Server
To understand the working of choose() function and case statements in SQL Server.
Let’s first understand the syntax (how we write choose() function and case statements in SQL Server) then we will check the performance between the two with bulk data.
Consider a table called Emp_Details having columns Id, Name, and DOB(date of birth).
CREATE TABLE Emp_Details
(
Id int,
Name varchar(20),
DOB date
);
Let’s add some data to Emp_Details
INSERT INTO Emp_Details VALUES
(1,’Mark’,’1980–01–11'),(2,’John’,’1981–12–12'),(3,’Amy’,’1979–11–21'),
(4,’Ben’,’1978–05–14'),(5,’Sara’,’1970–03–17'),(6,’David’,’1978–04–05');
Problem Statement:- Write an SQL query to fetch Name, DOB, and MonthName from DOB.
Solution:-
- By using CHOOSE() function
SELECT Name, DOB,
CHOOSE(DATEPART(MONTH,DOB),’Jan’,’Feb’,’March’,’April’,’May’,’April’
,’May’,’June’,’July’,’Aug’,’Sep’,’Oct’,’Nov’,’Dec’) as [Month]
FROM Emp_Details;
2. By using CASE statements
SELECT Name, DOB,
CASE DATEPART(MONTH, DOB)
WHEN 1 THEN ‘Jan’
WHEN 2 THEN ‘Feb’
WHEN 3 THEN ‘March’
WHEN 4 THEN ‘April’
WHEN 5 THEN ‘May’
WHEN 6 THEN ‘June’
WHEN 7 THEN ‘July’
WHEN 8 THEN ‘Aug’
WHEN 9 THEN ‘Sep’
WHEN 10 THEN ‘Oct’
WHEN 11 THEN ‘Nov’
WHEN 12 THEN ‘Dec’
END
AS [Month] FROM Emp_Details
Now, let us add bulk data to Emp_Details to check which is the more optimized CHOOSE() or Case statements to write queries.
Initial Records in the table:
SCRIPT FOR BULK DATA INSERTION
BEGIN
DECLARE @Row_Count NVARCHAR(20)
DECLARE @Id INT=1
SET NOCOUNT ON
PRINT(‘Data Insertion In Emp_Details Table’)
WHILE(@Id<=100000)
BEGIN
INSERT INTO Emp_Details
VALUES
(@Id,’Name -’+ CAST(@Id AS NVARCHAR(20)),GETDATE())
SET @ID=@ID+1
END
SET @Row_Count = (SELECT COUNT(*) FROM Emp_Details);
PRINT(‘TOTAL RECORDS INSERTED ‘ + @Row_Count)
END
Records in the table after the above script execution:
Time taken by Choose() function to fetch 100000 entries
Time taken by Case statements to fetch 100000 entries
Do clap, if find useful!🙂
