There are two types of partitioning in SQL Server – Vertical & Horizontal.
Today, we are quickly going to see these two types in SQL Server and its different use cases.
One of the easiest way to explain Vertical partitioning is a partition at Column level otherwise Horizontal Partitioning is at Row level.
Vertical Partitioning
Vertical Partitioning is a process of splitting a really big table into multiple smaller tables for various reasons. Performance improvement is the major benefit of vertical partitioning. If your table is having large number of columns and lots of Large Object columns or very lengthy textual information, then partitioning the table into multiple smaller tables would really help to boost the performance especially if not all of your columns are frequently accessed. A simple pictorial representation as below:
Lets quickly see how vertical partitioning helps in performance.
(Note that this is a very simple example for readers to understand the impact of query execution). Few guys will have different arguments, please post those
questions in comment, we will discuss in upcoming blog post. But for now, I strictly wanted to limit the scope of this post ONLY to the context of vertical partitioning.
Script:
Drop table if exists Employee_Tab1
Create Table Employee_Tab1
(EmployeeID int,FirstName varchar(10),
MiddleName varchar(10)
,LastName varchar(10)
,DOB Date
)DECLARE @i int
SET @i = 1
BEGIN TRAN
WHILE @i<>100000
BEGIN
INSERT INTO Employee_Tab1
VALUES(@i,'a' ,'b','c', getdate())
SET @i=@i+1
END
COMMIT TRAN
GO
Set statistics io on
Select EmployeeID,DOB From Employee_Tab1
where dob = cast(getdate() as date)
Set statistics io off |
Drop table if exists Employee
Create Table Employee
(EmployeeID int,FirstName varchar(10),
MiddleName varchar(10),
LastName varchar(10),
DOB Date,Address1 nvarchar(max),
Address2 nvarchar(max),
Address3 nvarchar(max))
DECLARE @i int
SET @i = 1
BEGIN TRAN
WHILE @i<>100000
BEGIN
INSERT INTO Employee
VALUES(@i,'a' ,'b','c',
getdate(),Replicate('a',8000),
Replicate('a',8000),Replicate('a',8000))
SET @i=@i+1
END
COMMIT TRAN
GO
Set statistics io on
Select EmployeeID,DOB From Employee
where dob = cast(getdate() as date)
Set statistics io off |
Points to ponder:
Use Vertical partitioning 1. If your table is having more columns 2. If your table has lots of BLOB or wide text columns those are not frequently used 3. If your table is a highly transaction table 4. with extreme care if you are restructuring your table since it may have big impact like many objects may need to change.Horizontal Partitioning
As mentioned earlier, horizontal partitioning is a partition process that carries out at row level. That means, if you a table with (m/b)illions of data, you can partition the table horizontally. This would enable better manageability/maintainability etc. Horizontal partitioning may also have performance benefits, however, we should know how to implement this technique efficiently. Let us quickly see a pictorial representation of the Horizontal partitioning as below.
Horizontal Partitioning requires understanding of few important terminologies.
1. Partition Key Column
Partition Key column determines on what basis the table has to be partitioned. In our example, the partition key column is AuditDate. It is important to be noted that the choice of partition key column is very important for performance aspects. We will cover this in details in next blog posts.
2. Partition Function
Just partition key column does not partition the table. We need a partition logic to be implemented on key column to partition the table. This logic can be implemented through a function called partition function. We can implement the function using RANGE Right and RANGE Left. Simply illustration of Range as below.
-
1. Range left means that the actual boundary value belongs to its left partition, it is the last value in the left partition.
2. Range right means that the actual boundary value belongs to its right partition, it is the first value in the right partition.
Points to ponder:
Use Horizontal partitioning 1. If you know your table and data in detail (I am not joking – 🙂 ) 2. If your table has lots of data and you have a definite partition requirement 3. If your table needs an archival of data or efficient movement of data We will cover more on this topic in coming days, until then stay tuned!Refer Also:
Stairway to SQL Server Table Partitioning – How do we partition an existing table I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
SQLZealots 