Partitioning In SQL Database

Partitioning allows you to improve SQL Server read/write performance by distributing a table over multiple databases or servers.Partitioning is the database process or method where very large tables and indexes are divided in multiple smaller parts.But even though the object is physically partitioned into number of units, it still can be used as one logical object.SQL Server 2008 introduced partition table parallelism for better performance and for better resource utilization. With SQL Server 2012, we are now allowed to even create up to a 15K partition on a single table.

Why we need to use partitions

1.When the data in the table continues to grow,it is very difficult to manage single large table. For example, if there is maintence in the database like backup or restore then the time taken for non-partitioned table will take more time to complete the task.
2.On a large table,only few data will be written and remaining data is used for read purpose.So we need more indexes for read only data whereas we need less indexes for read-write data.If the table are partitioned this task can be done easily.
3.Creating and maintaining indexes on a single large table takes significantly longer time and even increases the downtime window.
4.Backup and restore takes significantly longer as it needs to back or restore the whole data every time.
5.Frequent lock escalation issue at table level.
6.When we have a table with data that should be archived once in a while, we can take the oldest partition from that table and save it as the newest partition in the archive table quickly.

SQL Server partitioning is typically done at the table level, and a database is considered partitioned when groups of related tables have been distributed. Tables are normally partitioned horizontally or vertically.

Vertical partitioning

In a vertically partitioned table, columns are removed from the main table and placed in child tables through a process called denormalization. This type of partitioning allows you to fit more rows on a database page, making tables narrower to improve data-access performance. Therefore, a single I/O operation will return more rows. By vertically partitioning your data, you may have to resort to joins to return the denormalized columns.

SQL Server by default have vertically partition text and image datatype columns on the database page. Columns created using the text and image datatype will be stored in separate data pages from the non-text and image columns. That way they take up less space than if you stored the text data in columns using the varchar data type.

Horizontal partitioning

A horizontally positioned table distributes groups of rows into different tables or databases according to a criteria, which may include a primary key value, date column or a location identifier.

In most horizontal partitioning implementations, each server or database contains a subset of the data. Prior to SQL Server 7, these implementations were done at the application level where the calling application would make decisions on which table to access based on the SARG (Search Argument). If tables were partitioned within the same database, the T-SQL logic had to account for the partitions so the correct partition was referenced or updated. Such partitioning methods quickly became complex and did not scale well. If you created a view to present a complete results set, all the underlying base tables would have to be referenced.

To conclude Both partitioning methods have their strengths and weaknesses.Vertical partitioning is used to narrow the effective width of your rows through denormalization and careful use of text datatype columns. Horizontal partitioning breaks VLDB (very large database) tables into smaller tables according to ranges.