Vertical PartitioningVertical 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.
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 PartitioningAs 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!