Table Partitioning Implementation with advanced scenarios (Part 1)


HI …. Today I will write in very wonderful topic SQL Server Partitioning and my post will be as Repository for the beginner and for Expert because I will cover the main and most important point and the most cases can be happened by Partitioning , Partitioning Is big story with big history and no one can cover avery thing on it but i will do my best to cover the most important point in Table Partitioning.

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?

Concept of data Partitioing

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.images
  • 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. images (2)
  • 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 thisLink

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.

Vertical PartitioningThe 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

figure2 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.

Follow me

LinkedIn , Community , ONE HINDERED POST ,

Advertisements

About Mustafa EL-Masry

I love fixing problems and learning new technologies that can help me fix those problems faster and better than I did the time before. In my spare time I like to read, speak, write blogs and learn new things. I currently work as a Principal Database Administrator where I solve fairly interesting problems on fairly large databases. And troubleshooting High sensitive Performance Cases ► I am Microsoft Certified in SQL Server Administration and Development 2008 , 2012 MCTS, MCTIP , MCSA , MCSE ► I have deep practical knowledge about T-SQL performance , HW Performance issues, Data Warehousing and data mart solutions , SQL Server Replication, Clustering solutions (Active Active and active passive)and Database Designs for different kinds of systems , HAG, I worked on All SQL Server Versions (2005,2008,2008R2,2012,2014) diving deeply more check the below information about me : ► Founder of Community :SQL Server Consultation :http://MostafaElmasry.com/ ► Co-Founder : http://www.SQLGULF.net ► Audience Marketing Manager and Executive Board member: SQLSERVER PERFORMANCE TUNING http://sqlserver-performance-tuning.net/ ► LinkedIn: https://www.linkedin.com/in/mostafaelmasry ► My Community Annual report : http://mostafaelmasry.com/2014/annual-report/ ► I am Technical Writer and Reviewer : http://www.slideshare.net/MostafaElmasry3/ ► Fluent Participator at Microsoft Forums of SQL Server at http://Social.technet.microsoft.com ► +200 blogs in SQL Server technology English and Arabic: http://mostafaelmasry.com/community-founder/all-tips/ ► Microsoft Profile: https://www.mcpvirtualbusinesscard.com/VBCServer/EngMostafaElamsry/profile For any inquiries you can contact me on my phone or by mail: Mobile: +966 543990968, +2 01114668821 Mail: dbconsultant@mostafaelmasry.com & SQLGULF@mostafaelmasry.com
Gallery | This entry was posted in SQL Server 2012 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s