Will SQL 2008 query opmtimizer optimize this query?

Question

Have I to break this query

SELECT
   ISNULL(SUM(AF.[amount]), 0) AS [firm],
   ISNULL(SUM(ACA.[amount]), 0) AS [cash],
   ISNULL(SUM(AC.[amount]), 0) AS [client],
   ISNULL(SUM(AFR.[amount]), 0) AS [fr],
   ISNULL((SUM(AF.[amount]) + SUM(ACA.[amount]) - (SUM(AC.[amount]) + SUM(AFR.[amount])), 0) AS [total]
FROM ...

into two:

DECLARE @firm DECIMAL(14,2), @client DECIMAL(14,2), @fr DECIMAL(14,2), @cash DECIMAL(14,2)

SELECT @firm = SUM(AF.[amount]), @client = SUM(AC.[amount]), @fr = SUM(AFR.[amount]), @cash = SUM(ACA.[amount])
FROM ...

SELECT
   ISNULL(@firm, 0) AS [firm],
   ISNULL(@cash, 0) AS [cash],
   ISNULL(@client, 0) AS [client],
   ISNULL(@fr, 0) AS [fr],
   ISNULL((@firm + @cash) - (@client + @fr), 0) AS [total]

?

Answer 1

I don't think that you can say this for sure. The query engine decides what it optimizes based on the current circumstances.

But, honestly, this is micro optimization. It won't make a big difference anyway. I think the first query is easier to understand and to maintain, so I'd stick with it.

If you really want to know if it is optimized in your case, just write both queries and look at the execution plans. But even so, the query engine could change it's "mind" later on.

Answer 2

The ISNULL would normally be trivial compared to the aggregate. No difference in practice.

Another way: optimization applies to the set based statement. The 2nd form with separate ISNULLs won't be optimized: it's a simply assignment.

However, they could give different outputs if no rows are returned by the FROM/WHERE clauses. The ISNULL won't execute in the first one, you'll get zeroes for the seconds.