Partitioning
From SQLServerPedia
|
See Also: Main_Page - Database Administration - Database Design - Partitioning SQL Server 2005 introduced native support for partitioned tables and indexes. The concept of partitioning isn't new. Earlier versions of SQL Server attempted to implement similar functionality using multiple tables combined in views with UNION ALL construct. Starting with SQL Server 7, partitioning could also be implemented using distributed partitioned views. Partitioned tables and indexes add tremendous manageability and performance improvements compared to their predecessors. SQL Server Partitioning Tutorial VideoIn this ten-minute video, Brent Ozar explains the basics of partitioning and why it helps with Very Large Databases (VLDBs). http://tutorials.sqlserverpedia.com/SQLServerPedia-20090706-Partitioning.flv When To Use SQL Server PartitioningPartitioning implementation with SQL Server 2005 is straightforward and makes tables with millions and billions of rows more manageable. But partitioning does add complexity and some administrative overhead to the database. You should consider partitioning existing tables if performing backups and maintenance on large tables cannot fit in the maintenance time window defined by your customers. Partitioning could also be a clever option for a table if adding or removing a large number of rows from this table takes a long time. Last but not least, partitioning tables can also offer significant performance improvements for queries that retrieve only small portions of a large table.
How to Implement Partitioned TablesNote that SQL Server 2005 partitioning divides tables horizontally; each partition has all columns of the table, but only contains a subset of all data. Occasionally you might also wish to split tables vertically. In that case, you would create two tables; each with a subset of columns. For example, if you have four VARCHAR(2000) columns in a table you could include the primary key column and two VARCHAR(2000) columns in each table. Doing so will keep the size of data rows relatively small. The smaller the row size, the more of such rows you could expect SQL Server to cache, which in turn reduces the number of input / output (I/O) operations required by each data retrieval or data modification statement. So splitting tables which have several large columns vertically could also help tune performance. You can subsequently partition both tables horizontally for further performance improvements and better manageability.
|