Index on computed columns in sql server

A computed column is computed from an expression that can use other columns in the same table. It is a very powerful feature of SQL Server to store calculated values through manipulation on other columns. A computed column is computed from an expression and this expression can use one or more columns in the same table. The expression can be a non-computed column name, constant, function, and any combination of these connected by one or more operators but the sub query can’t be used for computed column.

For example to calculate the total price we are using Quantity and price

Total price = Quantity * price

There are two type of Computed Column

Ø  Persisted

Ø  Non-Persisted

By default computed column is none persisted.

 Read more about Computed columns:  Computed Column in SQL Server

https://bageshkumarbagi-msbi.blogspot.com/2017/07/computed-column-in-sql-server.html

We can create an index on a computed column, which is a virtual column that is not physically stored in the table (unless it's persisted) but is computed from other columns in the table. Indexing computed columns can be particularly useful for improving the performance of queries that frequently use expressions involving those columns. If column is non-persisted then when we creating on index it will automatically convert that column into persisted.

Let’s see the example.

Creating a table

Create table tblOrder ( OrderID bigint not null primary key, Qty int, Price decimal(18,2), Total_Price as Qty * price persisted )

 Now inserting 50k records into this table 

declare @i int = 1, @a int = 2, @b int = 10, @c int = 50,@d int = 100; while @i <= 50000 begin insert into tblOrder(OrderID, Qty, Price) select   @i,   FLOOR( @a + RAND()*(@b - @a) ), FLOOR(@c + RAND()*(@d - @c) ) set   @i = @i + 1; end

Records inserted successfully.

Let get the data from this table. Running the below query

select * from tblOrder where Total_Price=248.00

 

See the total execution time: 49 ms

Logical read: 381 pages 

IO Cost:  0.283125

Let’s create a non-cluster index on the completed column.

create nonclustered index idx_tblorder_total_price on tblorder (total_price);

Index created successfully.

Let’s run the same query.

Execution time : 1 ms

Logical Read: 2 pages

Io Cost: 0.003125

Indexing computed columns in SQL Server can significantly enhance query performance, particularly for frequently used expressions or calculations. It is a useful technique for optimizing queries that involve complex or repetitive calculations. By creating a computed column and indexing it, we can reduce CPU overhead, improve query speed, and make our database operations more efficient.