Simple vs Searched CASE statements

Simple CASE statement

CASE Sales_Amount

    WHEN 0 THEN 0

    WHEN 1 THEN 1

    WHEN 2 THEN 2

    WHEN 3 THEN 3

    ELSE 4

END

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

• Allows only an equality check.
• Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.
• Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
• If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

https://msdn.microsoft.com/en-us/library/windows/desktop/ms722762%28v=vs.85%29.aspx


Searched CASE statement

CASE

    WHEN Sales_Amount = 0 THEN 0

    WHEN Sales_Amount = 1 THEN 1

    WHEN Sales_Amount = 2 THEN 2

    WHEN Sales_Amount = 3 THEN 3

    ELSE 4

END

The searched CASE evaluates the conditions independently under each of the "when" options. With this structure, far more complex conditions can be implemented with a searched CASE than a simple CASE.

• A searched CASE can combine multiple tests using several columns, comparisons and AND/OR operators.
• Evaluates, in the order specified, Boolean_expression for each WHEN clause.
• Returns result_expression of the first Boolean_expression that evaluates to TRUE.
• If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

https://msdn.microsoft.com/en-us/library/windows/desktop/ms715950%28v=vs.85%29.aspx

WARNINGRemember that in both simple and searched CASE constructs, the conditions are evaluated sequentially from top to bottom, and execution exits after the first match is found. So, suppose more than one condition is true, only the first action is considered.

SQL Server allows for only 10 levels of nesting in CASE expressions.


Performance

Basically, a searched CASE requires all previous branch conditions to have been evaluated before a given branch is chosen, which in turn makes it harder for the query optimiser to evaluate a searched CASE in bulk mode. As a result it’s always better to use a simple CASE where possible; in the worst scenarios (and I guess this is what was happening to my customer) the performance of a searched CASE can be several times worse than a simple CASE even when they seem to be doing the same thing.


http://blog.crossjoin.co.uk/2009/12/09/simple-vs-searched-case-statements/ 
http://beyondrelational.com/modules/2/blogs/115/posts/11160/simple-vs-searched-case-expression.aspx
https://msdn.microsoft.com/en-us/library/ms181765.aspx