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:
CREATE FUNCTION dbo.fn_GetTotalCourseFee ( @CourseFee NUMERIC(9, 2), @TaxPercentage NUMERIC(9, 2) ) RETURNS NUMERIC(9, 2) WITH SCHEMABINDING AS BEGIN DECLARE @TotalFee NUMERIC(9, 2) = 0.0 SET @TotalFee =… More