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

Summary
You should realize that delivering a high performance database that will also scale is largely dependent
on proper schema design. The highlights of this chapter are:

❑ Using normalization you can minimize the amount of storage required as well as mitigate sev-

eral data quality anomalies.

❑ Understanding the data page will serve as a constant guide as you design your tables.

❑ Choosing the smallest data types that will fit the need will provide tremendous benefits for your

tables. This will go a long way to maximizing row density, which is key.

Placing large value data types in the right place, either in-row or out-of-row, will also contribute
to getting the most out of your data pages.

❑

Use partitioning can also reap many benefits.

❑

Adding constraints will enforce data quality.

❑

Create stored procedures to minimize recompiling and execution plan generation.

❑

Use triggers to augment constraints as well as providing a place to put complex business logic.

❑

Use concurrency properly. Relieve the lock manager from any unnecessary work.

❑

Use indexes properly, especially clustered indexes.

❑