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

As you can tell from this information about pages, it's imperative that you design your tables to
maximize page density. This means using the smallest data type that will accommodate the needs of a
column. This also means using variable-length data types wisely. The space saved by variable-length
data types can be substantial. However, there's additional overhead surrounding variable-length data
types. If a column's data doesn't vary greatly within the rows of a table, then a variable-length data type
shouldn't be used.
Partitioning
The purpose of partitioning is simple. The idea is to segregate a table's rows and columns based on
popularity. A table's most popular rows and columns are known as hot . The goal is to group those
rows and columns together so that access can be fast. Furthermore, the counter-goal is to get the least pop-
ular rows and columns out of the way. Let's face it, year-old order information in an order-entry system
is less useful than week-old information. Partitioning tables has always been possible with SQL Server.
However, SQL Server 2005 adds some tools and utilities, which make the task much easier. There are two
types of partitioning which can be applied to a table. They are horizontal and vertical partitioning.
Vertical
Vertical partitioning involves splitting a table's columns into multiple tables. Splitting one table into
multiple tables for the purpose of grouping the columns does not violate the idea of normalization.
Remember that normalization essentially means that tables should have a single purpose. If the table
you're splitting has a single purpose, then the resulting ''sub-tables'' should also have a single purpose.
Let's revisit the ''Operator'' table from the normalization example. See Figure 8-19.
Figure 8-19
Suppose that you learn that the Name and Callsign columns are very popular and the LicenseID,
ExaminerID, and RegionID aren't. You might split the table into two, as shown in Figure 8-20.
Now to round out the change a view called Operator can be created that mimics the original table. This
would allow any existing coding against the Operator table to work unaltered. Also, if the columns in
the second table aren't referenced in a query they won't be accessed.