Table Partitioning Implementation with advanced scenarios (Part 1)

Agenda and overview 

• What is table partitioning?
• Why we need to use table partitioning?
• PROS and CONS of table partitioning?
• Partitioned Table Rules and Gotchas
• What is difference Table partitioning Type in SQL Server?
  • Vertical Partitioning
  • Horizontal Partitioning
• How to Implement Partitioned Tables Step by Step With Multiple Scenario
  • Create File Group
  • Create partitioned Function (Range Right / Range Left)
  • Create partitioned Schema
  • Table Partitioned for new Table
  • Partitioned Existing table
  • Index Partitioned

• Check partitioned Function and Schema using T-SQL
  • List all Partitions
  • get partition id with Partition Number
  • partitioned Columns
  • Partition table Detail
  • FileGroup Detail

• Special Conditions for Partitions with advanced Cases
  • MERGE
  • Split
  • SWITCH OUT / SWITCH IN

• Manage multiple partitions in multiple filegroups
• Table partitioning  improvement in SQL Server 2014
• Performance Benefits with Partitioning
• References

What is table partitioning?

Table partitioning consists in a technique adopted by some database management systems to deal with large databases. Instead of a single table storage location, they split your table in several files for quicker queries. If you have a table which will store large amounts of data (I mean REALLY large amounts, like millions of records) table partitioning will be a good option. Queries that access only a fraction of the data can run faster because there is less data to scan.

Why we need to use table partitioning?

Where tables and indexes become very large, partitioning can help by splitting large amounts of data into smaller more manageable chunks, the type of partitioning described in this post is termed horizontal partitioning. With horizontal partitioning, large chunks of rows will be stored in multiple separate partitions. The definition of the partitioned set is customized, defined, and managed – by your needs. Partitioning in SQL Server 2005 allows you to partition your tables based on specific data usage patterns using defined ranges.

PROS AND CONS OF PARTITIONING IN SQL SERVER?

PROS:

• Low overhead (locks, speed) in sliding large amounts of data into separate table for truncation (our primary concern)
• Increased performance.
• No need to create separate physical tables for each ranges manually.
• There are few limitations when you insert data into Partition View like you cannot use BULK INSERT/BCP etc.
• Less complex to manage and administrate
• SQL Server automatically manages the placement of data in the proper partitions.

CONS:

• Major disadvantage is, we cannot have different index model for different range…In general, and we may need to have more number of indexes when the data is READ ONLY and less or different index model for the data which is READWRITE. That is not possible with partition table.
• You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.
• Additional data required in indexes (and queries) to have aligned indexes
• Other limitations associated with aligned indexes?

Table Partitioned Rules

• You cannot change the data type of the partitioning column once the table is partitioned.
•  
• You can move an index to a partition scheme after it has been created. 
• You cannot use the SWITCH option of ALTER TABLE statement on replicated tables.
• For a unique key or primary key index to be partitioned, the partitioning key must be part of the unique key (or the index key).
• Partitioning key of an index doesn’t have to be part of the index key.

For More information check this Link

What is difference partitioning Type on SQL Server table?

We have two kind of partitioning in SQL Server Vertical Partitioning and Horizontal Partitioning let’s go deeply to know what is the difference between Vertical Partitioning and Horizontal Partitioning on SQL Server Table .

For more Information about Horizontal Partitioning, Vertical Partitioning and Hardware Partitioning go for this Link

Vertical Partitioning:

Vertical partitioning segments columns into multiple tables containing the same rows Vertical partitioning divides a table into multiple tables that contain fewer columns.

The two types of vertical partitioning are normalization and row splitting: by removing some columns from the primary table to Secondary table that are linked to the primary table by primary key and foreign key relationships. We cannot decide Vertical partitioning is good or bad from the performance because it’s based on x of reasons ( Column Data Type , Column Size , Volume of data …) so it’s Ultimately not good not bad. As we can see from the image based on our requirement and our Queries hitting this table we can diced how we can splitting the Table by this way we will reduce the IO and CPU for the query scan

When I can use  Vertical Partitioning:

If I had 100 columns (or some large number) and didn’t often query some of them, or queried others very often, then I can save I/O and unneeded reads by moving some data to another table. Like the Example on the image 

Horizontal Partitioning:

Horizontal partitioning divides a table into multiple tables.

Each table have the same columns (Same table structure but the data distributed in this multiple tables according the

 Rang partition so if we have Millions of record we can partitioned horizontally into Multiple tables so any Query hitting our data will select from the Specific table and by this we will fast the Query Execution and the IO.