Computed Columns

Good afternoon everyone! Today I am going to be discussing Computed Columns and how they can be implemented to speed up your queries.

What Is A Computed Column?

A Computed Column is a combination of other columns within the same table. Once you have created a Computed Column, you can index it.

In order to speed up your queries, a computed column with an index applied will make queries use an index or table seek rather than an index/table scan which adds more to the query cost (for more information on these, please visit my previous blog post on Graphical Execution Plans)

How Do I Define and Index a Computed Column?

Firstly, you will be starting off with a query similar to the one below. It's searching through my Item table and it's counting the number of items that were created in October (10th month of the year).

I decide to add a Non-Clustered Index to the column to speed up the query in the first instance: 

And this is the Execution Plan as seen below: 

As you can see, we have an Index Scan there which could potentially slow our query down if it becomes larger (so has more conditions in the WHERE clause and has JOINs added) and if it's bringing back more data. However, if you create a computed column like in the example below, you can change this Index Scan into a more economical Index Seek: 

Next, add a Non-Clustered Index onto this new column: 

Now when we try and run the same query as we did in the beginning: 

We retrieve the following Execution Plan:

This is now an Index Seek rather than an Index Scan. Note that I didn't have to reference the new computed column in the new query. This is a handy feature in SQL Server to make it possible to add computed columns to an index without having to modify any SQL Statements or queries to use the new index, it picks it up automatically.

You will now notice an increase in speed between the 2 different queries! This may seem negligible for smaller databases but when you're bringing back large data sets and then add as many computed columns as possible, you will notice a significant improvement to your query performance.

What Next...

Tomorrow, I will be discussing Partitioning Tables - which is quite a large but important subject as they reduce the amount of work needed to be performed, therefore also speeding up the performance of the query.

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.