Successful Deployment Strategies - Professional SQL Server 2005 Performance Tuning - page 530

tool, in addition to allowing version control, also allows for rules to be set to ensure standardization of
check-in code. Schema changes can be disruptive, and it is always a good idea to have the current objects
scripted out and stored in a versioning repository such as VSS before applying any changes. This also
helps in quick reversal as opposed to completely restoring the database. However, it is always important
to have a full backup of your database before making any changes, irrespective of the size and impact of
the changes.
Best Practice
Always track changes so that it is much easier to retrace your steps in case something
goes wrong.
Dealing with Large Tables
It is quite normal these days for database tables to contain tens of millions of rows. Although they cer-
tainly help store large amounts of data, they can be quite a maintenance nightmare. SQL Server 2005
introduced the concept of partitioning tables and indexes to facilitate easier management as well as
better performance when dealing with tables with a high number of rows. If you anticipate that your
production database is going to have very large tables and if you want to take advantage of the new
partitioning functionality, it is best to make such plans during the database design stage.
When a table is partitioned, the data in the table is split horizontally into buckets that can then be placed
in different file groups and over different set of drives if need be. The partitions of a table or indexes are
treated as a single logical entity when querying or updating the data. It is important to keep in mind
while partitioning the data the manner in which the data is going to be queried. If most queries against a
table involve selection based on last name, then it is useless to partition the data by date.
Partitioning is also a great choice when only parts of a table are used for updates, inserts, and deletes
while the rest is used primarily for selects. For example, consider a very large table that contains order-related
information dating back to 2001. If the current month's data is used for updates or deletes and the rest of
the older data is used for selects, then it would be ideal to partition the data by month. This would also
be useful when performing maintenance operations as defragmentation and index rebuilds can happen
on the current month's partition while the older read only data continues to be available for querying.
Had partitioning not been used in this case, performing maintenance operations would involve tying up
the entire table.
Partitions are also used when performing data archiving and provide a very quick mechanism of
transferring data from a transactional table to an archive table for reporting purposes. This is achieved
through the process of partition switching, wherein the actual data is not moved; instead metadata is
changed to the new location where the data is being stored.
The following sections discuss some code related to partitioning tables.
Partition Functions
Creation of a partition function is the first step to partitioning tables. A partition function tells SQL Server
how to partition the data.