Today, we are going to learn how we can create index on a computed column which improves the performance of SELECT queries. To create a computed column, we will use an user defined function instead of inline code. Also to know about computed columns, refer my previous blog post “What is Computed Column in sql server“.
Let’s create a function, “dbo.fn_GetTotalCourseFee“, which accepts @CourseFee and @TaxPercentage as input parameters and returns the computed @TotalFee amount. To highlight some important prerequisites related to index creation on computed column, I have used a function, otherwise we can also use the inline code in column definition.
Computed column is a virtual column in sql server, which is not stored physically in the table. It’s data is not materialized in the database and gets calculated during query execution on that column.
It takes it’s value from an expression and that expression can contain other columns to calculate a new value for the computed column.
Can it persist physically in the belonging table?
Yes, and to make it persist physically in table, just mark it as PERSISTED. By default it does not persist physically. In this case the data will be materialized in the table and will not be computed during query execution.
We can not target this column during insert and update.
Default, Foreign key or NOT NULL constraints can not be applied to this column until it’s marked as PERSISTED.