Indexes on Computed Columns

Indexes on Computed Columns in SQL Server

Indexes on Computed Columns in SQL Server

In this post, I will explain all about Indexes on Computed Columns in SQL Server end to end along with appropriate examples. So let’s start –

Indexes on Computed Columns in SQL Server

Indexes on Compound Column: Indexes on compound columns allow us to index the result of a function or an expression that will improve the performance of queries whose WHERE clause contains the function and expression. We can use an index on a compound column in Microsoft SQL Server to achieve the similar effect of a function-based index –

  1. Create a compound column based on the expression on WHERE clause
  2. Create a non clustered index for the compound column

Let’s understand this by some example, we have a table name – Students in which available columns are – ID, Name, Email, Mobile, Address, City, State, and PinCode. Now we will execute a query to find the students name and Mobile whose local part of the email is – “ram.Ji”

Syntax:

SELECT Name,Mobile
FROM dbo.Students
WHERE SUBSTRING (email,0,
CHARINDEX(‘@’,email,0))=’ram.ji’

Result:

NameMobile
Ram9956267687

As we can above result that there is only a student available in the Student table whose local part of the email is – “ram.ji”. Now we can see the estimated execution plan of the query as shown in below snapshot –

Indexes on Computed Columns in SQL Server

We can see clearly in the above snapshot the query optimizer needs to scan the whole clustered index for locating the Student, which is not an efficient way to fetch the data.

To implement the Indexes on Computed Columns in SQL Server we can use the below steps (to search for students based on the local part of their email address) –

Synatx:

ALTER TABLE dbo.Students
ADD email_local_Part AS
SUBSTRING (email,0, CHARINDEX(‘@’,email,0))

After addition of this column in Students table, we have to create an index on the email_local_part column with below query –

Query:

CREATE INDEX ix_Student_email_local_part
ON dbo.Students(email_local_part)

Once created this index ix_Student_email_local_part we can use the email_local_part column instead of the expression in the WHERE clause to find the Students by the local part of the email address as shown in below query –

Query:

SELECT Name,Mobile,email
FROM dbo.Students
WHERE email_local_part = ‘ram.ji’

Now the query optimizer will use the index seek operation of the ix_Student_email_local_part index.

Requirements for Indexes on Compound Columns

Following are the requirements must be met to create an index on the compound column –

  1. The functions involved in the compound column expression must have the same owner as the table
  2. The compound column expression must be deterministic, means expression always returns the same result for a given set of inputs
  3. The compound column must be precise means its expression must not contain any float or real data types
  4. The result of the compound expression can not evaluate to the TEXT, NTEXT or Image data types
  5. The ANSI_NULLS option must be set to ON when the compound column is defined using the CREATE TABLE or ALTER TABLE statement passed. In addition, the options ANSI_PADDING, ANSI_WARNING, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON and NUMERIC_ROUNDBORT must be set to OFF

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc as it may help some other needy peoples too.

Leave a Reply