CODEWITHSUNDEEP
sqlsql-servercalculated-columns
Aly Elhaddad
Aly Elhaddad

Reputation: 1943

How to avoid computed-column duplicate computation in an SQL Query

I have the following query:

Use Northwind 

select OrderID as 'Order ID', (UnitPrice * Quantity) as 'Total', case
when (UnitPrice * Quantity) > 100 then 'Good' else 'Bad'
end as 'Rating' from [Order Details]

Theoretically, it computes (UnitPrice * Quantity) two times, and I believe that this is a bad choice for the performance.

How to accomplish this (avoiding the duplicate computation) in an elegant way?

Upvotes: 1

Views: 117

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Add computed column:

CREATE TABLE [Order Details]
     (..., UnitPrice INT, Quantity INT, Total AS UnitPrice * Quantity PERSISTED);


SELECT 
    OrderID AS 'Order ID',
    Total,
    CASE
       WHEN Total > 100 THEN 'Good'
       ELSE 'Bad'
    END AS 'Rating'
FROM [Order Details];

Upvotes: 1

Related Questions