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

In the first test, let's measure the performance impact of the persisted calculated column during batch insert
operation. The code for this is shown in Listing 4-22.

Listing 4-22. Calculated columns and UDF: Comparing the performance of batch insert operations

insert into dbo.NonPersistedColumn(ID)
select ID from dbo.InputData;

insert into dbo.PersistedColumn(ID)
select ID from dbo.InputData;

The execution time on my computer is shown in Table 4-2 .

Table 4-2. Batch insert performance
dbo.NonPersistedColumn dbo.PersistedColumn

100 ms 449ms

As a next step, let's compare the performance of the queries, which references the persisted and non-persisted
calculated columns during the SELECT operation using the code shown in Listing 4-23.

Listing 4-23. Calculated columns and UDF: Comparing the performance of SELECT operations

select count(*)
from dbo.NonPersistedColumn
where NonPersistedColumn = 42;

select count(*)
from dbo.PersistedColumn
where PersistedColumn = 42;

In the case of a non-persisted calculated column, SQL Server calls the user-defined function to evaluate the
predicate on every row, which significantly increases the execution time, as shown in Table 4-3 .

Table 4-3. Select performance with warm cache
dbo.NonPersistedColumn

dbo.PersistedColumn

7 ms 218ms

The noticeable performance impact is mainly related to user-defined function call overhead. However, you
would still have a performance impact due to the calculations, granted of a smaller scope, even when user-defined
functions are not used.

■ We will discuss user-defined functions and their performance implications in greater depth in Chapter 10,
“user-defined Functions.”

Note