CODEWITHSUNDEEP
sqlsql-serverstored-proceduresdatabase-performance
Jason596
Jason596

Reputation: 43

Case when versus if else statement

Can someone explain if there is any performance difference between "case when" and "if-else" in SQL Server? And in which scenario should I use which statement?

IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
       SELECT 'Weekend';
ELSE 
       SELECT 'Weekday';
   

select
case when DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday') 
        then 'Weekend'
    else 
             'Weekday'
 end

Upvotes: 0

Views: 4115

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12969

I tested both queries in Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) . There is no performance difference between them, except that SELECT CASE is little ahead in milliseconds.

SELECT GETUTCDATE()
GO
--IF DATENAME(SECOND, GETDATE()) IN (N'Saturday', N'Sunday')
--       SELECT 'Weekend';
--ELSE 
--       SELECT 'Weekday';
select
case when DATENAME(SECOND, GETDATE()) IN (N'Saturday', N'Sunday') 
        then 'Weekend'
    else 
    'Weekday'
 end
GO 50
SELECT GETUTCDATE()
GO

For IF ELSE logic:

SELECT DATEDIFF(MILLISECOND, '2020-07-09 04:40:21.170','2020-07-09 04:40:27.693')

--6523 milliseconds

For CASE logic: SELECT DATEDIFF(MILLISECOND, '2020-07-09 04:41:35.580','2020-07-09 04:41:41.973')

--6393 milliseconds

But, as @Dale K, mentioned, they are generally having different use cases. SELECT helps in setting multiple variable values at a single time. If Else logic, you have to set values separately. If else logic, can have lots of business logic internally. SELECT CASE is a single statment.

Upvotes: 2

gourav bhattacharya
gourav bhattacharya

Reputation: 5

The case statement will work properly for a performance basis. Because CASE statement we can directly use in any select query without specifying Transact-SQL syntax, but if you are going to use IF statement in which it will work as Transact-SQL query syntax(as you have used SELECT statement twice), but in case you have to use it once only.

Upvotes: -1

Related Questions