MS SQL - CASE vs IF performance

Question

I have a function in SQL that performs a check against one of a number of tables based on an input parameter. E.G.

CREATE FUNCTION demo
(
    @Classification INT,
    @ClassificationValue INT
)
RETURNS INT
AS
BEGIN

    IF @Classification = 1
    BEGIN
        IF EXISTS (SELECT CountryRegionId FROM table_1 WHERE id = @ClassificationValue)
        BEGIN
            RETURN 1;
        END
    END 
    IF @Classification = 2
    BEGIN
        IF EXISTS (SELECT CountryRegionId FROM table_2 WHERE id = @ClassificationValue)
        BEGIN
            RETURN 1;
        END
    END 
    RETURN 0;
END

This is a simplified situation, and in reality there are more target tables to choose from.

MY Question

In C# or most other languages it would be better to use a switch statement rather than successive if statements, as a hash would be used. Is this the same in SQL (given that case statements can contain logical expressions - e.g. Case When a <5 - obviously can't be hashed.

Answer 1

The cost of the comparison in the if is, essentially, nothing compared to the cost of the exists statement -- even with an index on the appropriate keys.

Don't worry about such micro-optimizations unless you literally have thousands and thousands of comparisons.

In that case, I would first recommend ordering the comparisons by the most likely-to-match first. Then, I would suggest looking into an alternative data structure where you can compare the id in a single table. Having tables that are so similar is often a sign that you can improve the data structure with fewer bigger tables.

And, finally, if you are really concerned about the number of comparisons, use nested ifs to get O(log n) comparisons. As an example, "8" comparisons would have the structure:

if @Classification < 5
    if @Classification < 3
        if @Classification = 1
        else . . .
    else if @Classification = 3 . . .
    else . . .
else . . .