Computed columns in SQL Server are a powerful feature that allow you to define a column in a table that is computed based on an expression or formula. They can be used to simplify queries and calculations, and can even be indexed for improved performance.

In a previous blog post, we discussed the concept of computed columns and how they can impact storage space. In this article, we will explore another aspect of computed columns – their impact on performance.

When a computed column is defined as PERSISTED, SQL Server stores the computed value in the table, rather than calculating it at runtime. This can significantly improve performance, especially when the computation is complex or resource-intensive.

Let’s consider an example to understand the performance benefits of persisted computed columns. In the example, we have a table called CompCol with three columns – ID, FirstName, and LastName. We also have a similar table called CompCol_P with an additional persisted computed column called FullName_P.

We insert 100,000 records into both tables and then add a computed column called FullName to CompCol and a persisted computed column called FullName_P to CompCol_P. The computation for both columns is the same – the power of the length of the first three characters of the concatenation of FirstName and ID.

Next, we run a SELECT statement on both tables to compare the performance. With the non-persisted computed column, you can observe an additional operator in the execution plan, indicating that the column value is calculated at runtime. On the other hand, the persisted computed column does not require this additional computation, resulting in improved performance.

By examining the execution plan and the associated costs, we can clearly see that the persisted computed column has a lower cost compared to the non-persisted column. This is because the values of the persisted column are pre-calculated and stored in the table, eliminating the need for runtime computation.

In conclusion, when dealing with complex computations in SQL Server, using persisted computed columns can greatly improve performance. By pre-calculating and storing the computed values, SQL Server avoids the overhead of runtime computation, resulting in faster query execution.

Stay tuned for more articles in this series on computed columns, where we will explore other aspects such as indexing and storage considerations.