A common requirement for dealing with large datasets is the ability to split the data into smaller blocks to help improve performance. Performance degradation is evident once the tables reach larger sizes, and therefore, it is sometimes appropriate to break large tables using a process called partitioning or horizontal partitioning, which helps to extend and improve the database performance. Partitions not only improves database performance, but also improves the manageability, availability of a wide variety of database applications, reducing the total cost of ownership for storing large amounts of data. Moreover, there are several reasons to partition relational data.

Fortunately, like other conventional commercial database systems, SQL Server 2005 or later versions provides a rich variety of partitioning strategies such as the ability to create partitions for large tables across multiple filegroups. Moreover, as it is totally transparent, the partition can be applied to almost any OLAP (Online Analytical Processing) or OLTP (On-line Transaction Processing) applications without the need for application changes potentially expensive and time consuming. Only SQL Server Enterprise and Developer editions support partitioned tables. SQL Server partitioned table provides various benefits such as better performance, less performance impact during table management and better control over backup and restore.

Checkout my first part of three part article series on partitioning relational data here. In this part, you’ll learn why you might want to partition your relational data. You’ll also learn how to partition tables and how to partition indexes to match partitioned tables.

This article is published on SSWUG.org.