DbSchema | SQL Server - How to Implement Partitioning?

SQL Server • 01-Jul-2023

by Malik Umair Sajjad

Introduction
Prerequisites
What is Partitioning?
Usage of Partitioning
Advantages and Limitations of Partitioning
Restrictions and Permissions
Types of Partitioning
Difference between Horizontal and Vertical Partitioning
Implementing Partitioning in sqlcmd
Implementing Partitioning in DbSchema
Conclusion
References

Introduction

In the world of databases, handling large amounts of data effectively and efficiently can be a challenging task. Partitioning is one approach that can greatly enhance the performance of data manipulation and retrieval processes in SQL Server. This article explores the concept of partitioning in SQL Server, its advantages, limitations, different types, and how to implement it using sqlcmd and DbSchema.

Prerequisites

Before diving into this tutorial, it’s assumed that you have:

A basic understanding of SQL and SQL Server.
SQL Server installed and configured.
sqlcmd utility and DbSchema installed.
A sample database to use for demonstration.

For installation and establishing connection you can read our article SQL Server-How to create a database?

What is Partitioning?

Partitioning is a data management technique that allows you to divide a very large table into smaller, more manageable parts called partitions. Each partition is stored separately, potentially on different physical storage devices. These partitions can be independently managed and queried, making data retrieval and management more efficient.

Usage of Partitioning

Partitioning is typically used in the following scenarios:

When dealing with large tables or indexes to improve their manageability and performance.
For simplifying maintenance operations by enabling them to target specific portions of a table.
To enhance the speed of certain queries by allowing the database engine to access data from individual partitions.

Advantages and Limitations of Partitioning

Advantages

Performance improvement: Partitioning can greatly enhance query performance, particularly for large tables, by enabling more rapid data access.
Manageability: Large tables can be divided into smaller, more manageable parts.
Enhanced data availability: Since partitions can be independently managed, the failure of one doesn’t impact the others.

Limitations

Complexity: Partitioning adds complexity to database design and administration.
Cost: Depending on the database management system and storage architecture, implementing partitioning may entail additional costs.
Inappropriate usage: If not used correctly, partitioning can decrease rather than increase performance.

Restrictions and Permissions

For creating and managing partitions, users must have the ALTER permission on the table or index, and the CONTROL permission on the database. Un-partitioned tables cannot be directly altered into partitioned tables. They first have to be converted into a partition function and scheme.

Types of Partitioning

Partitioning can be divided into three main types:

_Type_	_Description_
`Horizontal Partitioning`	This involves dividing a table into multiple tables. Each table then contains the same number of columns, but fewer rows.
`Vertical Partitioning`	Here, the table is divided along column lines. Each resulting table has fewer columns, but contains the same number of rows.
`Hybrid` (Combination of Horizontal and Vertical)	A combination of both horizontal and vertical partitioning, which divides a table both in terms of rows and columns.

Difference between Horizontal and Vertical Partitioning

*Criteria*	*Horizontal Partitioning*	*Vertical Partitioning*
`Division`	Rows	Columns
`Purpose`	To enhance performance and manageability of large records	To improve performance and security by reducing disk I/O
`Example`	Historical data in a table can be moved to another table	Sensitive columns can be moved to a separate table

Implementing Partitioning in sqlcmd

Implementing partitioning in sqlcmd involves creating a partition function, a partition scheme, and applying these to a table. For this demonstration, we’re going to create a sample database named TestDB and a table named Orders.

Launch your command prompt and start sqlcmd by typing sqlcmd.

sqlcmd

Connect to your SQL Server instance using the following command

sqlcmd -S <server_name> -U <username> -P <password>

Replace server_name with your actual server name, and username and password with your SQL Server credentials.

Now, let’s create a sample database named TestDB.

CREATE DATABASE TestDB;
GO

Use the TestDB database for subsequent commands.

USE TestDB;
GO

Create a table named Orders with three columns.

CREATE TABLE Orders
(
OrderID int NOT NULL,
OrderDate date NOT NULL,
Amount decimal NOT NULL
);
GO

To know more about creating a table you can read our article SQL Server-How to create a table?

Insert some records into the Orders table.

INSERT INTO Orders VALUES (101, '2023-07-01', 1000.00);
INSERT INTO Orders VALUES (201, '2023-07-02', 2000.00);
INSERT INTO Orders VALUES (301, '2023-07-03', 1500.00);
GO

Create a partition function. This function will divide the table data into partitions. We’ll name this function OrdersPF. We’ll partition our data based on the OrderID column.

CREATE PARTITION FUNCTION OrdersPF (int)
AS RANGE LEFT FOR VALUES (100, 200, 300, 400);
GO

This function creates 5 partitions. The first will hold rows with values from -infinity to 100, the second from 101 to 200, the third from 201 to 300, the fourth from 301 to 400, and the last for 401 to infinity.

Next, create a partition scheme. This will map the partitions to filegroups. We’ll name our scheme OrdersPS.

CREATE PARTITION SCHEME OrdersPS
AS PARTITION OrdersPF
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);
GO

Modify the Orders table to use the partition scheme. We’ll create a clustered index on OrderID, the column we’re using to partition our data.

CREATE CLUSTERED INDEX idx_OrderID
ON Orders (OrderID)
ON OrdersPS (OrderID);
GO

Now let’s see how our data is distributed among the partitions. We can check this using the system view sys.partitions.

SELECT $PARTITION.OrdersPF(OrderID) AS Partition, COUNT(*) AS [COUNT]
FROM Orders
GROUP BY $PARTITION.OrdersPF(OrderID)
ORDER BY Partition;
GO

This query will show you how many rows are in each partition. It’ll help you understand how your data has been divided.

Sample Database:

Let’s consider we have a table named Orders with the following columns: OrderID, OrderDate, and Amount. Let’s say the table has the following data:

*OrderID*	*OrderDate*	*Amount*
`101`	2023-07-01	1000.00
`201`	2023-07-02	2000.00
`301`	2023-07-03	1500.00
`401`	2023-07-04	2500.00
`501`	2023-07-05	3500.00

Now, we’re going to implement partitioning on this Orders table based on the OrderID column. Using the partition function and partition scheme explained earlier, our table will be divided into 5 partitions:

Partition 1: Rows with OrderID from -infinity to 100
Partition 2: Rows with OrderID from 101 to 200
Partition 3: Rows with OrderID from 201 to 300
Partition 4: Rows with OrderID from 301 to 400
Partition 5: Rows with OrderID from 401 to infinity

Here’s how our data will be distributed among these partitions:

*Partition*	COUNT
`1`	0
`2`	1
`3`	1
`4`	1
`5`	2

Partition 1 has no rows as none of our OrderID values fall into the -infinity to 100 range.
Partition 2, 3, and 4 each have 1 row with OrderID values falling in their respective ranges.
Partition 5 has 2 rows because it contains OrderID values from 401 to infinity. Our OrderI values 401 and 501 fall into this range.

Implementing Partitioning in DbSchema

Implementing partitioning in DbSchema is quite visual and straightforward. Here are the steps:

Open DbSchema and connect to your SQL Server database.
Open the Relational Data Browse feature from the toolbar.
Right-click on the table you want to partition and select Edit Table.
In the table editor, select the Partitions tab.
Define the partition type, partition key, and other parameters as per your requirements.
Apply the changes and close the editor.
To check the partition, you can use the SQL Query tool to run queries against your partitioned table.

Visually Manage SQL Server using DbSchema

DbSchema is a SQL Server client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

Partitioning is a powerful technique that enhances data management and query performance on SQL Server. With a clear understanding of its benefits, limitations, and how to implement it using tools such as sqlcmd and DbSchema, you can effectively handle large volumes of data.