Understanding the concept of Table Partitioning in SQL Server

In this article we look at the concept of table portioning as implemented in Ms SQL Server

Partitioning in SQL Server involves partitioning tables and indexes and storing them in multiple physical sections. This feature is available in the Enterprise edition of SQL Server, if you wish to use it for production. It is also available in the developer edition for trial. Partitioning is a complex feature of SQL Server, and allows splitting an index into multiple small indexes. Every partition has equal number of columns and different number of rows. Partition in SQL Server is transparent in the sense that clustered and non- clustered index can be referred to as a single structure.

Since partitioning is a complicated feature it is difficult to explain its functioning and how all of its components work together. Here is a list of basic features of partitioning in SQL Server.

1. Switch – Partition can be switched in and out of a table, leading to faster removal and loading of large data. This is currently the best feature in Partition. Schema Modification lock or SCH-M lock is required for faster switching of partition. But this might also get you blocked, disabling you from loading and removing data from the table for a period longer than you can afford.
   

2. Partition elimination – After applying partition in either of the tables, the SQL Server Query Optimizer attempts to direct a query to not go through the entire table and instead go through a single partition or multiple partitions. There are records for entire partitioned table but no additional steps in histogram, so the SQL Server Query optimizer will not know how much information the query will return thus slowing down operation as the information increases.

3. Partition Management – Every single partition can be:

• Built separately for both clustered and non clustered indexes.
• Allow you to optimize back up by being set at ‘read- only’ through file group.
• Available on multiple disk sub- system, occasionally used data will be available on slower disk and regularly used data will be available on faster disk, in a single table. Partition can be shifted to slower or faster disk through virtualization or SAN solutions.

4. Columnstore Indexes – These are most sought for in the 2012 edition of SQL Server as they enable blazing fast performance. These indexes are ‘read only’ however partitions can be switched into them.

Many people are confused about whether they should opt for partition in their tables or not? They are often unsure about whether their table requires a partition? In such a scenario it is important to understand the problem you are facing. Partitioning is not inexpensive, since it is available only in enterprise edition and not in standard edition. Many people opt for partition in order to add on to the scalability but scalability does not require such an expensive and time taking solution. Partition can definitely add on to the scalability but there are cheap solutions available as well depending upon the health, performance and flexibility of your database.

A SQL Server administrator must prepare in advance to deal with chances of SQL data file corruption

Incidents of SQL database file corruption or SQL crashes that comprise the underlying data are quite common. To deal with such situations, a SQL Server administrator must prepare in advance by keeping a sql recovery software like DataNumen SQL Recovery handy. This tool can be called into action whenever a SQL database files gets corrupted and it can assuredly get back all data while maintaining complete data integrity. Moreover the tool can also be used to recover several files in one go.

Author Introduction:

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/