SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.


Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...