Special Indexing and Storage Features - Pro SQL Server Internals - page 96

Calculated columns, which are using user-defined functions, prevent Query Optimizer from generating parallel
execution plans even when queries do not reference them. This is one of design limitations of Query Optimizer. We
can see this behavior if we run the query shown in Listing 4-24. The code uses undocumented trace flag 8649, which
forces SQL Server to produce a parallel execution plan if it is possible. As usual, be careful with undocumented trace
flags and do not use them in production.
Listing 4-24. Calculated columns and parallel execution plans
select count(*)
from dbo.NonPersistedColumn
option (querytraceon 8649);
select count(*)
from dbo.PersistedColumn
option (querytraceon 8649);
select count(*)
from dbo.InputData
option (querytraceon 8649);
As you can see in Figure 4-13 , the only time when SQL Server was able to generate a parallel execution plan is in
the table without a calculated column. It is worth mentioning that SQL Server is able to generate parallel execution
plans for tables with calculated columns, as long as they are not calculated with user-defined functions.
Figure 4-13. Calculated columns and parallel execution plans
You can create indexes on calculated columns even when those columns are not persisted. This is a great option
when the main use-case for a calculated column is to support Index Seek operations. One such example is searching
by the last four digits in a SSN. You may consider creating a nonclustered index on the SSNLastFour calculated
column in the Customers table (shown in Listing 4-19) without making the calculated column persisted. Such an
approach saves storage space for data.