How to optimize repeated calculations across columns in SQL Server?

Data and Databases
1

I’m working with a SQL Server database and I’ve got a table called orders with these fields:

orders (Price, Discount, Shipping, AddOn1, AddOn2, AddOn3)

My current query looks like this:

SELECT 
(Price * Discount + Shipping) + AddOn1 AS TotalCost1,
(Price * Discount + Shipping) + AddOn2 AS TotalCost2,
(Price * Discount + Shipping) + AddOn3 AS TotalCost3
FROM orders

I’m wondering if there’s a way to calculate (Price * Discount + Shipping) just once and use that result in all three columns. Something like this:

SELECT
base_cost + AddOn1 AS TotalCost1,
base_cost + AddOn2 AS TotalCost2,
base_cost + AddOn3 AS TotalCost3

Is there a way to do this in SQL Server without using a variable for each row? I’m looking to make my query more efficient. Any ideas would be great. Thanks!

2

You can optimize your query by using a Common Table Expression (CTE). This approach calculates the base cost once and reuses it, improving efficiency. Here’s how you can do it:

WITH base_costs AS (
    SELECT 
        (Price * Discount + Shipping) AS base_cost,
        AddOn1, AddOn2, AddOn3
    FROM orders
)
SELECT 
    base_cost + AddOn1 AS TotalCost1,
    base_cost + AddOn2 AS TotalCost2,
    base_cost + AddOn3 AS TotalCost3
FROM base_costs

This method avoids repetitive calculations, potentially improving query performance, especially for large datasets. It’s clean, readable, and doesn’t require variables for each row.

3

hmmm have u thought about using a CROSS APPLY? consider this:

SELECT ca.base_cost + AddOn1 AS TotalCost1,
ca.base_cost + AddOn2 AS TotalCost2,
ca.base_cost + AddOn3 AS TotalCost3
FROM orders
CROSS APPLY (SELECT Price * Discount + Shipping AS base_cost) ca

does this work for u?

4

hey, have u tried using a derived table? It’s pretty simple:

SELECT
x.base + AddOn1 AS TotalCost1,
x.base + AddOn2 AS TotalCost2,
x.base + AddOn3 AS TotalCost3
FROM (
SELECT *, Price * Discount + Shipping AS base
FROM orders
) x

this way u only calculate the base cost once. hope that helps!