SQL Server Computed Index

In previous post, we have learnt about Index and its types. In this post, we will learn about computed index. Computed Index is created on computed column of table or view.

In SQL Server, you can create an index on a computed column using the following syntax:

CREATE INDEX [IndexName]
ON TableName (ComputedColumnName);

Here, [IndexName] is the name you want to give to the index, TableName is the name of the table, and ComputedColumnName is the name of the computed column on which you want to create the index.

Example. Suppose you have a table called Sales with columns Quantity and Price, and you want to create a computed column TotalPrice that represents the total value of the sale (Quantity * Price). You can then create an index on the computed column:

First, we create computed column in Sales table.

ALTER TABLE Sales
ADD TotalPrice AS Quantity * Price;

Next, we create an index on the computed column.

CREATE INDEX IX_Sales_TotalPrice
ON Sales (TotalPrice);

Points to remember:

When creating an index on a computed column, keep in mind that the computed column must be deterministic and precise. It means that the result of the computation must be the same every time it is calculated for a given set of input values, and the computation must not involve non-deterministic functions, subqueries, or other non-deterministic elements.

In the next post we will learn about Schema in SQL Server.