SQL CASE vs multiplication

Question

I have a query like this:

SELECT
  CASE WHEN IsAdd = 1
  THEN
    Base + Extra
  ELSE
    Base
  END
FROM MyTable

Would using multiplication to eliminate the CASE statement have better performance?

SELECT
    Base + Extra * ISNULL(IsAdd, 0)
FROM MyTable

Answer 1

You don't need to worry about such optimizations.

The expense of a SQL query is usually around reading the data, extracting the records, and returning the results. Simple operations such as multiplication or comparisons are minor. Some operations can get expensive (such as working with user-defined functions, long strings, and so on). But multiplication would not generally be one of them.

So, focus on what best represents the work you want to accomplish. Your two queries do not express exactly the same logic. They may happen to be the same if IsAdd only takes on the values of 0, 1, and NULL. But you should use the version that best expresses your intent.

By the way, I prefer COALESCE() to ISNULL() because it is standard.