Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Quick Case Statement Quiz
Published: Jun 10, 2016
Everyone uses the case statement… I see it all over the place and, to be honest, nearly everyone uses it completely correctly.

However, there are those instances in which I see it used incredibly badly so I thought I’d make it into a quick quiz. (Although it’s not really long enough to be a quiz, and it contains answers… therefore more just me showing examples really).

So let’s dive in to some simple uses of the Case Statement and see if we can predict the output…

When will this statement cease evaluating and return our result?

declare @int int = 1

select case
when @int = 1 then 'Bob'
when @int = 2 then 'Jeff'
end as result

If you said “It will stop processing when it resolves, therefore after the first WHEN” then you are correct.

This is exactly what we expect the Case Statement to do and why we use it.

So… knowing this, what will happen with this case statement?

declare @int int = 1

select case
when @int = 1 then 100
when @int = 2 then 1/0
end as result

Correct… it will evaluate at 1, return the answer, and then stop. All is well.

So what about this?

declare @int int = 1

select case
when @int = 1 then 100
when @int = 2 then max(1/0)
end as result

If you said this will work, then you’re sadly incorrect… this will fail.

The reason for this is because SQL Server seems to process aggregates before it performs the evaluation. Therefore it performs the aggregate “max(1/0)” which fails, and therefore the statement is never even executed.

This really isn’t a common occurrence to be honest, just something to be aware of in case you ever come across it.

Next, what about this?

declare @int int = 1

select case @int
when 1 then 100
when 2 then 999
end as result

Correct… this will return 100 as we expect.

So why did I mention this? Well, it’s more to point out that internally SQL Server actually re-writes our statement as follows:

declare @int int = 1

select case
when @int = 1 then 100
when @int = 2 then 999
else null
end as result

Again, why is this important?

Well, consider the following:

select case convert(tinyint, 1+rand()*3)
when 1 then 1
when 2 then 2
when 3 then 3 end

This doesn’t look bad so go ahead and run it a few times. What you should find is that you could end up seeing the following:

We know that our rand statement can only ever return 1, 2, or 3… so how can we get NULL as an answer?

This is why I made mention above as to how SQL Server re-writes this internally…

select case when convert(tinyint, 1+rand()*3) = 1 then 1
when convert(tinyint, 1+rand()*3) = 2 then 2
when convert(tinyint, 1+rand()*3) = 3 then 3
else null end

Now it should become obvious. SQL Server calculates the rand value at every step. Therefore we could plausibly have the first case evaluation be 3 so SQL moves to the second WHEN and re-evaluates… this time it calculates 1… then for the last WHEN it calculates 2… all that’s left is the default result of NULL.

Therefore be VERY careful if you use non-deterministic functions in a case statement!!! Not understanding how they work can cause some very unexpected and unwelcome results.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.