Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 206

Consider the following query and plan (shown in Figure 8-9) against the SalesOrderHeader table in
AdventureWorks.
SELECT YEAR(OrderDate) OrderYear, COUNT(*)
FROM Sales.SalesOrderHeader
WHERE MONTH(OrderDate) = 4 AND YEAR(OrderDate) = 2002
GROUP BY YEAR(OrderDate)
Figure 8-9
Next, apply the following changes to the table and look at the new query plan (shown in Figure 8-10).
ALTER TABLE Sales.SalesOrderHeader ADD
OrderYear AS YEAR(OrderDate),
OrderMonth AS MONTH(OrderDate)
CREATE INDEX IX_OrderYear ON Sales.SalesOrderHeader (OrderYear)
CREATE INDEX IX_OrderMonth ON Sales.SalesOrderHeader (OrderMonth)
Figure 8-10
Notice how the optimizer now picked up on the indexes created on the newly added computed columns.
With a very small use of additional disk space by the indexes, this query has gained a substantial
performance improvement.
ColumnOptions
Choosing the correct data types, normalizing tables, defining primary keys, and so on contributes to
improving the quality of your data. However, your work in this area isn't finished. Another aspect of
quality has to do with the integrity of the data. Ensuring this integrity doesn't come for free. Ensuring that
data meets all the requirements for accuracy can be detrimental to performance. For example, inserting a
new row into a table with a foreign key constraint requires that the SQL Server engine check that the key
value is valid, based on values in the parent table. This will be slower than if the foreign key constraint
didn't exist.
To illustrate this consider the tables shown in Figure 8-11.