Database Reference
In-Depth Information
Listing 4-18. Correlated predicates: Creating filtered statistics
create statistics stat_Cars_Toyota_Models
on dbo.Cars(Model)
where Make='Toyota'
Now if you run select statement again, you would have a correct cardinality estimation, as shown in Figure 4-12 .
Figure 4-12. Cardinality estimation with filtered statistics
The limitations of filtered statistics are similar to filtered indexes. SQL Server would not use it for cardinality
estimation in the case of cached plans when there is the possibility that filtered statistics would not be applicable for
all possible choices of parameters. One of the cases where this happens is autoparameterization, when SQL Server
replaces constant values in a WHERE clause of a query with parameters; that is, SQL Server would not use statistics if
it autoparameterizes the predicate on the Model column in the query. A statement-level recompile can help to avoid
such a situation. Moreover, SQL Server does not count the modifications of filter columns towards the statistics
modification threshold, which requires you to update statistics manually in some cases.
Note
We will discuss plan caching and autoparameterization in greater depth in Chapter 26, “plan Caching.”
Calculated Columns
SQL Server allows you to define calculated columns in a table using expressions or system and scalar user-defined
functions. Listing 4-19 shows an example of a table with two calculated columns.
Listing 4-19. Table with two calculated columns
create table dbo.Customers
(
CustomerId int not null,
SSN char(11) not null,
Phone varchar(32) null,
SSNLastFour as (right(SSN,4)),
PhoneAreaCode as (dbo.ExtractAreaCode(Phone)),
/* Other Columns */
);
 
 
Search WWH ::




Custom Search