Partitioning Relational Data – (Part – 3)

This is the third part of the three part article series on partitioning relational data. 

In the first part of this three-part article series on partitioning relation data, I started the article by giving an introduction to partition relational data along with the reasons why you might want to partition relational data. Then I discussed how to partition tables and how to match indexes to match partitioned tables. In the end, I discussed the rules that must be taken into account when partitioning tables and indexes.

In the second part of partitioning relational data article series, I discussed and demonstrated how you can convert existing table to partition table, add, modify, remove or switch partitions, and how you can view data in a specific partition.

Checkout the third and last part of partitioning relational data article series here. In this part, I discussed why one might want to partition multidimensional data, overview of SQL Server partitioning architecture, and guidelines for partitioning relational and multidimensional data.

This article is published on SSWUG.org.

Partitioning Relational Data – (Part – 2)

This is the second part of the three part article series on partitioning relational data. 

In the first part of this three-part article series on partitioning relation data, I gave an introduction to partition relational data along with the reasons why someone might want to partition relational data. Then I discussed how to partition tables and how to match indexes to match partitioned tables. In the end I discussed the rules that must be taken into account when partitioning tables and indexes. 

Checkout the second part of partitioning relational data article series here. In this part I discussed how you can convert existing table to partition table, add, modify, remove or switch partitions, and how you can view data in a specific partition.

This article is published on SSWUG.org.

Partitioning Relational Data – (Part – 1)

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.