DbSchema Database Designer

DbSchema | SQL Server - How to Implement Partitioning?

SQL Server alt >

Table of Contents

  1. Introduction
  2. Prerequisites
  3. What is Partitioning?
  4. Usage of Partitioning
  5. Advantages and Limitations of Partitioning
  6. Restrictions and Permissions
  7. Types of Partitioning
  8. Difference between Horizontal and Vertical Partitioning
  9. Implementing Partitioning in sqlcmd
  10. Implementing Partitioning in DbSchema
  11. Conclusion
  12. 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.

  1. Launch your command prompt and start sqlcmd by typing sqlcmd.
sqlcmd
  1. 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.

  1. Now, let’s create a sample database named TestDB.
    CREATE DATABASE TestDB;
    GO
  1. Use the TestDB database for subsequent commands.
    USE TestDB;
    GO
  1. 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?

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

  1. 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
  1. 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
  1. 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:

  1. Open DbSchema and connect to your SQL Server database.
  2. Open the Relational Data Browse feature from the toolbar.
  3. Right-click on the table you want to partition and select Edit Table.
  4. In the table editor, select the Partitions tab.
  5. Define the partition type, partition key, and other parameters as per your requirements.
  6. Apply the changes and close the editor.
  7. 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.

References

  1. Microsoft Docs: Partitioned Tables and Indexes
  2. DbSchema Documentation: DbSchema Diagram Designer & Admin Tool
DbSchema Features

DbSchema → Your Trusted Partner in Database Design

Simplify complex database workflows and improve productivity with DbSchema's advanced design and management tools

Visual Design & Modeling
Visual Design & Schema Layout

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.