Database Reference
In-Depth Information
The code shown in Listing 4-25 creates an index on a non-persisted calculated column and references this
column in the query.
Listing 4-25.
Indexing a non-persisted calculated column
create unique nonclustered index IDX_Customers_SSNLastFour
on dbo.Customers(SSNLastFour)
go
select *
from dbo.Customers
where SSNLastFour = '1234';
Figure
4-14
shows the execution plan for the select statement. As you can see, SQL Server is able to use the index.
Figure 4-14.
Execution plan that utilizes an index on a non-persisted calculated column
One of the common use-cases where calculated columns are beneficial is with XML. Shredding XML is a slow
and CPU-intensive operation. XML indexes can improve the performance of queries, although they use a large
amount of storage space in the process.
As an alternate solution, you can add persisted calculated columns that shred and store information from the
most commonly used XML nodes. Furthermore, you can index those columns if needed. This approach can improve
the performance of queries without the storage overhead that XML indexes introduce. Unfortunately, SQL Server does
not allow you to use XQuery in calculated column expressions, and you must use a scalar user-defined function to
shred XML. As already discussed, this prevents SQL Server from using parallel execution plans with such tables.
■
Note
We will talk about xMl in Chapter 11, “xMl.”
It is always important to decide where to calculate data. Even though calculated columns are convenient for
developers, they add load to SQL Server during calculations. It is even more important in cases when applications use
ORM frameworks and load calculated columns as attributes of the entities. This scenario increases the chance that
calculated columns will be referenced and calculated, even when they are not needed for some of the use-cases.
You also need to remember that a typical system includes multiple application servers with only one active
database server serving all of the data. It is usually simpler and cheaper to scale out application servers, rather than
upgrading the database server.
Calculating data at the application server or client level reduces the load on SQL Server. However, if the system
does not have dedicated data access and/or business logic tiers, it could lead to supportability issues when a
calculation needs to be done in multiple places in the code. As usual, the decision falls into the “It Depends” category,
and you need to evaluate the pros and cons of every approach.
