SQL Server Magazine

Editor's Note: Send your experts-only T-SQL tips to Itzik Ben-Gan at blackbelt@sqlmag.com. If we use your tip in the magazine, you'll receive $100 and an exclusive T-SQL Black Belt shirt.

Using expressions in queries is a common practice. But did you know that you can base a column in a query's resultset on a computation rather than simply using the query to return a base column from a table? If you use the same computation in many queries against the same table, storing the computation in the base table as a computed column shortens your queries and results in less required code maintenance. For example, suppose you need to calculate the gross and net values of each order row in the Northwind sample database's Order Details table. You could perform the computations as part of your query as follows:

SELECT
  OrderID,
  ProductID,
  Quantity,
  Quantity * UnitPrice AS GrossValue,
  Quantity * UnitPrice * (1 - Discount) AS NetValue
FROM [Order Details]

Or, if you have many different queries that retrieve the gross and net values, you might prefer to add GrossValue and NetValue computed columns to the Order Details table:

ALTER TABLE [Order Details]
  ADD GrossValue AS Quantity * UnitPrice,
      NetValue AS Quantity * UnitPrice * (1 - Discount)

Now you can refer to the GrossValue and NetValue columns in the same way that you'd refer to any other columns in your table:

SELECT OrderID, ProductID, Quantity, GrossValue, NetValue
FROM [Order Details]

Note that adding a computed column to a table doesn't necessarily mean that SQL Server stores the computation result on disk. By default, SQL Server evaluates the computation at runtime, when the query that refers to the computed column is invoked. SQL Server 7.0 introduced computed columns; SQL Server 2000 added the ability to create indexes on computed columns, so the results can be stored on disk. An index on a computed column saves the CPU time needed to perform the calculations and allows efficient filtering and sorting where appropriate.

   Prev. page   [1] 2     next page