T-SQL Tutorial

Indexes on computed columns

SQL Server indexes on computed columns are a type of index that can be created on a computed column, which is a column that is derived from an expression using other columns in the table. These indexes allow for faster querying of data based on the values in the computed column.

Creating an index on a computed column can be done using the CREATE INDEX statement and specifying the computed column in the index key. The computed column must be persisted, which means that the value of the computed column is stored in the table and does not have to be recalculated each time the row is read.

Indexes on computed columns can be useful when the computed column is frequently used in queries and the expression used to calculate the column is expensive. The index can improve query performance by allowing the database engine to quickly locate the rows that match a given value in the computed column.

It's important to note that indexes on computed columns can increase the size of the table and the update cost. Therefore, it's important to carefully consider whether an index on a computed column is necessary and monitor the performance of the indexes over time to ensure they are still providing the desired performance gains.


Example of creating an index on a computed column in SQL Server:

Suppose we have a table called "Orders" with columns "OrderID", "CustomerID", "OrderDate", and "TotalAmount". We want to create an index on a computed column called "DiscountedAmount", which is calculated as "TotalAmount" minus a discount percentage.

First, we need to add the computed column to the table:

OrderID INT,
CustomerID INT,
OrderDate DATE,
TotalAmount DECIMAL(18,2)

ADD DiscountedAmount AS TotalAmount * 0.9;

Next, we can create an index on the computed column like this:

CREATE INDEX IX_DiscountedAmount
ON Orders(DiscountedAmount);

This will create a non-clustered index on the "DiscountedAmount" column, which will allow SQL Server to quickly search for and retrieve rows based on the discounted amount.