CODEWITHSUNDEEP
sqlsql-servercase
Koosh
Koosh

Reputation: 896

SQL Case statement, which is most effective/quickest?

I'm trying to write a CASE statement in a VIEW and I am wondering which is the best/preferred way of doing it. I'm investigating a FIELD VALUE and want to assign a different value to it, depending on the Value of FIELD1, an example:

CASE WHEN Field1 = 'X' THEN 1
WHEN Field1 = 'Y' THEN 0
WHEN Field1 = 'Z' THEN 2
ELSE 4
END

or

CASE Field1
WHEN 'X' THEN 1
WHEN 'Y' THEN 0
WHEN 'Z' THEN 2
ELSE 4
END

Any help which is more proper?

This is running against a lot of data, i want this to be as quick as possible.

Upvotes: 0

Views: 847

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Both CASE expressions should generate the same execution plan/behavior. However, both do not offer the same flexibility in syntax. Your second version only allows you to do equality comparisons of Field1 to other literal values:

CASE Field1 WHEN 'X' THEN 1
            WHEN 'Y' THEN 0
            WHEN 'Z' THEN 2
            ELSE 4
END

However, the first style is more flexible, and lets you use any logical comparison involving Field1. For example, you could try:

CASE WHEN Field1 BETWEEN 1 AND 10 THEN 0
     WHEN Field1 > 10 THEN 1
     ELSE 2 END

Upvotes: 3

Related Questions