DbSchema Database Designer

DbSchema | SQL Server - How to Implement Partitioning?

Publish on DbSchema Blog >>>

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


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.


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


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


  • 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.
  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.
  1. Use the TestDB database for subsequent commands.
    USE TestDB;
  1. Create a table named Orders with three columns.
    OrderID int NOT NULL,
    OrderDate date NOT NULL,
    Amount decimal NOT NULL

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);
  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.
    AS RANGE LEFT FOR VALUES (100, 200, 300, 400);

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.
  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.
    ON Orders (OrderID)
    ON OrdersPS (OrderID);
  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
    ORDER BY Partition;

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.


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.


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

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.