Vertical Partition in SQL Server

PARTITION

• In SQL Server partition is the process of dividing a large table into smaller manageable parts.
• SQL Server supports table and index partitioning. The data of the partitioned table and index is divided into units across more than one filegroup.
• The data partitioned horizontally so that groups of rows are mapped into individual partitions.

BENEFITS OF PARTITIONING

• We can transfer and access data quickly and efficiently.
• We can improve query performance based on types of queries.
• We can perform maintenance operations on one or more partitions more quickly.

There are two types of Partition

• Vertical
• Horizontal

VERTICAL PARTITIONING:-

• Vertical partitioning divides a table into multiple tables that contain fewer columns.
• The two types of Vertical partitioning are Normalization and Row splitting.
• Normalization is a process of removing redundant columns from a table and putting them in other tables that linked with foreign key relationships.
• Row Splitting divides the original table vertically into tables with fewer columns.
• EXAMPLE

Here I have created the users' table. It contains 299398 records.

USERS TABLE

First, enable the statistics time and fetches the record. It returns 48796 records with CPU time 171 ms.

RESULT

Now we created another table is users1 with column DisplayName, Age, Location and Views.

USERS1 TABLE

After that, we fetch the record of the user1 table. It returns 48796 records with CPU time 32ms.

RESULT

Vertical partitioning improves the performance of accessing the data.

ISSUES OF VERTICAL PARTITIONING

• The user gets confused while fetching the different partition because of the access speed of partitions differ for each other.
• The anomalies in a third normal form such as insertion, deletion, and modification are possible.
• Due to replicated copies of data across the partitions, it consumes more space while comparing to data stored in normalized files.
• Modification of data affects data in multiple partitions because it consumes more time when one file is used.

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: