In this article we would go through the key concepts associated with Database partitioning in SQL Server
Partitioning in SQL Server is used for easy maintenance and improved performance of the database. It allows splitting of large tables along with individual tables. Queries which require access to limited data can be performed quickly as the data to be scanned is lesser. Maintenance task like rebuilding indexes or backing up tables, can be performed quickly. Partitioning does not always require splitting; you might also choose to put tables on individual disks. Query performance can be improved by putting one table on the physical disk and related tables on different disks.
Following are the different types of partitioning
- Hardware partitioning – This allows the database to take maximum advantage of the available hardware architecture. Following are its examples.
- Multiprocessors enabling several threads of operations, allowing multiple queries to run simultaneously. There is also scope for a single query to run faster on several processors by allowing several components of a query to run simultaneously.
- Redundant Array of Independent Disks (RAID) allows for stripping of data on several disks thus providing faster access to data, due to a greater number of read-write heads involved. Stripped tables are scanned quicker than tables that are stored on a single drive
- Horizontal Partitioning – This allows for division of a single table into several other tables, with existing number of columns but lesser number of rows. Horizontal partitioning is based on how you want data to be analyzed, partitioning in a way that lesser tables are referred for queries.
It is common to partition data horizontally on the basis of age and use. For instance – in a table with data from last five years, if you only need last year’s data or current year’s data, you can opt for partitioning the table year wise with each table consisting a year’s data.
- Vertical Partitioning – This is in a way the opposite of horizontal partitioning, because it divides the table vertically, with lesser columns and existing number of rows. But is also similar to it because this also improves query performance by allowing for scanning of lesser amount of data. Vertical partitioning can be further classified into two parts:
Row Splitting: This involves dividing the original table in a way that it has few columns.
Normalization: This is a regular process of removing unnecessary tables and placing them in secondary tables which are somehow connected to primary table. For instance- in a table with 7 columns, if only four are required, breaking the last 3 columns into a different table might be helpful.
Partitioning is tricky and expensive; it is not available in all editions of SQL Server. And the ones in which it is available, should be used after proper consideration. Partitioning the tables wrongly can create a lot of inconvenience not only to the Database Administrator but also to the enterprise.
The precious data stored in your SQL file can be compromised in the event of a crash
With the exception of very large enterprises, most firms running SQL Server instances are susceptible to a chance of data associated with a SQL crash if a proper failsafe protocol is not in place. The simplest way to get back your data involves using a mdf recovery tool like DataNumen SQL Recovery to extract records from the compromised file. This tool is way ahead of its peers and can handle data recovery from gigantic MDF files with minimum fuss. If that was not all, the tool can even recover several corrupted MDF files in one go.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/