Partitioning tables into different files contained in the same database is a good way to improve performance of...
large databases. You can choose to partition your tables using either vertical or horizontal partitioning depending upon the nature of your data.
Vertical partitioning takes the columns of a table in a database and places them in two or more other databases. Since the data comes from a single table and is contained in one location the resulting partition is more manageable than a situation in which several tables are in the same database file. A view set up from your main database should make the user experience identical to the previous set up. When you partition vertically you don't even have to change your code, only the pointers in the view to the location of the new files.
Very large databases are often narrow and deep with data – as is the case with image or large text data fields. In instances like that it is desirable to perform a vertical partition by breaking up your table by putting the large data columns into new columns that are placed into other databases. Any smaller columns can be retained in your original database as a unit.
It's important when you break your data apart in a vertical partitioning scheme to retain the relational integrity of your database. Depending upon your database's construction, that can be a design challenge. Since your data is now in several different places, your backups are more complicated than before and you need to take extra care. You want to backup all of your database's data at the same time, because that way you can perform a full restore of the data set in your partitioned data.
Vertical partitioning large datasets does have its issues though. Breaking your data apart into separate files means that every time you INSERT or DELETE you have to run multiple statements. DELETEs also must be run on each referenced table as SQL Server will not permit a DELETE from a view that points to multiple tables. So although the views are transparent to users, any modification of your code requires that you account for where the data was placed whenever you need to make changes.
So you can see that although vertical partitioning creates a performance benefit and a logical uniformity, it also creates operational complexity that you may or may not be able to live with. Next tip we will look at the alternative: horizontally partitioning your data.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.