Tag : computed column


Index on computed column 2

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“.

Create index on computed column

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.

Below is the function being used to create a computed column:

Note that the above function is created with schema bind clause “WITH SCHEMABINDING” which is must required to allow indexing on the computed column, otherwise … More


What is Computed Column in sql server 4

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.

Without physical persistence:

With physical persistence:

Limitations:

  1. We can not target this column during insert and update.
  2. Default, Foreign key or NOT NULL constraints can not be applied to this column until it’s marked as PERSISTED.

Benefits:

  1. Readability : In case of some complex calculation
More