Calculated columns

This lesson uses DDI_SQL_Training dataset version 1.0.0 on SQL Server 2019 or later. OrderItems stores Quantity and UnitPrice separately. A report can calculate the value of each line when it reads the table, without storing another column.

Run the example

SELECT TOP (3) OrderID, LineNumber,
       Quantity * UnitPrice AS LineTotal
FROM dbo.OrderItems
ORDER BY OrderID, LineNumber;

Read it step by step

1. Quantity * UnitPrice multiplies the two values from each OrderItems row. The * operator means multiplication in T-SQL.
2. AS LineTotal names that calculated result. It does not create a LineTotal column in dbo.OrderItems.
3. TOP (3) shows only three rows after ORDER BY OrderID, LineNumber. OrderID plus LineNumber is the table’s unique key, so the example always selects the same three lines.

Check the result

Order 1 has two lines, so its ID appears twice. Their calculated totals are 117.00 and 180.00. The next ordered row is line 1 of order 2, worth 516.00. A calculated column is evaluated for each returned row.

Practice

Create a result column named AnnualSalaryAfterRaise showing each employee salary multiplied by 1.05. Does this update Employees?

SELECT EmployeeID, AnnualSalary * 1.05 AS AnnualSalaryAfterRaise FROM dbo.Employees;