Sometimes, when you write a more complicated SELECT statement, you have to repeat the exact same formula two or even more times. Like in this simple example. In reality the formulas tends to be more lengty…
SELECT oh.CustomerID, SUM(oh.TaxAmt + oh.Freight) AS Extra, MAX(SUBSTRING(c.AccountNumber, 6,2)) AS Kontoklass FROM Sales.SalesOrderHeader oh INNER JOIN Sales.Customer c ON c.CustomerID = oh.CustomerID GROUP BY oh.CustomerID HAVING SUM(oh.TaxAmt + oh.Freight) > 10000 AND MAX(SUBSTRING(c.AccountNumber, 6,2)) = '30'
In this simple exeample you need to have the calculations in both the SELECT and GROUP BY parts of the query, because the SELECT clause is executed after the GROUP BY claus. Wouldn’t it be nice if we could have our formulas in just one place? We can! By defining the formulas in the FROM clause their aliases can be used in all other places. The trick is to use the APPLY clause and a derived table. This table can use columns from all previous tables in the statement as parameters and return one row with custom calculations. So, lets rewrite the query.
SELECT oh.CustomerID, SUM(Extra) AS Extra, MAX(Kontoklass) AS Kontoklass FROM Sales.SalesOrderHeader oh INNER JOIN Sales.Customer c ON c.CustomerID = oh.CustomerID CROSS APPLY (SELECT /********* Formulas *********/ oh.TaxAmt + oh.Freight AS Extra, SUBSTRING(c.AccountNumber, 6,2) AS Kontoklass ) AS t GROUP BY oh.CustomerID HAVING SUM(Extra) > 10000 AND MAX(Kontoklass) = '30'
Nice! This way we don’t need to repeat the formulas with copy and paste many times, and most important – it’s much easier to maintain the code in the future. But wait, what about performance? Don’t worry. In most cases (if not all) SQL Server will use the same execution plan.
If you have any SQL Server related problem – don’t hesitate to contact any of SQL Service consultants.