DbSchema Database Designer

DbSchema | SQL Server - How to Implement Database Sharding?

Publish on DbSchema Blog >>>

SQL Server: How to Implement Database Sharding in sqlcmd and DbSchema

SQL Server alt >

Table of Contents

  1. Introduction
  2. Prerequisites
  3. What is Database Sharding?
  4. Why Use Database Sharding?
  5. Permissions and Restrictions
  6. Advantages and Limitations
  7. Problems Solved by Database Sharding
  8. Difference Between Horizontal and Vertical Sharding
  9. Implementing Database Sharding in sqlcmd and DbSchema
  10. Conclusion
  11. References


In the modern data-driven landscape, efficient database management has become increasingly crucial. To improve performance, databases often employ a strategy called sharding. This article provides an in-depth explanation of database sharding and demonstrates how to implement it using SQL Server, sqlcmd, and DbSchema.


  • Basic knowledge of SQL Server and database management.
  • Understanding of SQL commands.
  • Access to SQL Server Management Studio or similar database management tool.
  • Familiarity with sqlcmd and DbSchema.

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

What is Database Sharding?

Database sharding is a type of database partitioning that separates large databases into smaller, faster, more easily managed parts called data shards. Each shard is held on a separate database server instance, spreading the load and reducing the response time.

Why Use Database Sharding?

Database sharding is often used to enhance the performance, load balancing, and resilience of database systems. It’s particularly beneficial for large databases that handle substantial data traffic and require high processing power.

Permissions and Restrictions

Implementing sharding requires significant planning and specialized knowledge of database architecture. It’s essential to have necessary permissions, such as database creation and manipulation, as well as the ability to manage and configure database servers.

Advantages and Limitations


  • Improved performance.
  • Enhanced load balancing.
  • Better availability and disaster recovery capabilities.


  • Complex implementation process.
  • Potential data consistency challenges.
  • Increased infrastructure costs.

Problems Solved by Database Sharding

Database sharding is a solution to several problems, including:

  • Performance bottlenecks.
  • High latency and slow query response times.
  • Inadequate data management in large databases.

Difference Between Horizontal and Vertical Sharding

Horizontal Sharding Vertical Sharding
Definition Divides a database into rows and distributes different rows across multiple databases. Divides a database into columns, and different columns are stored in different databases.
Use Case Useful when dealing with large amounts of data of the same type. Effective when different types of data need to be accessed independently.

Implementing Database Sharding in sqlcmd and DbSchema

In sqlcmd:

In the following example, we’ll be using a sample database table “Orders” with columns OrderId, CustomerId, OrderDate, ProductId, and Quantity.

Step 1: Connect to SQL Server using sqlcmd

To connect to SQL Server using sqlcmd, you have to open your terminal (Command Prompt in Windows) and type:

sqlcmd -S localhost -U SA -P 'password'

In this command, -S defines the server to connect, -U defines the username, and -P defines the password. Replace localhost and password with your server’s name and password.

Step 2: Create New Databases for Sharding

Once connected, create two new databases that will act as our data shards. You do this by executing the following SQL commands:


The GO command signals the end of a batch of SQL statements.

Step 3: Create the Orders Table Schema in Each Database

Next, navigate to each database and create the Orders table:

USE OrdersDB1;
CREATE TABLE Orders (OrderId INT, CustomerId INT, OrderDate DATE, ProductId INT, Quantity INT);
USE OrdersDB2;
CREATE TABLE Orders (OrderId INT, CustomerId INT, OrderDate DATE, ProductId INT, Quantity INT);

Step 4: Distribute Data Across the Shards

Distributing data across the shards can be complex because it requires changes in your application layer to direct write queries to the correct shard based on your sharding strategy. Suppose we use CustomerId as our sharding key, and we distribute odd CustomerId to OrdersDB1 and even CustomerId to OrdersDB2. To simulate this, we can insert data manually:

For OrdersDB1:

USE OrdersDB1;
INSERT INTO Orders VALUES (1, 1, '2023-07-15', 10, 5);
INSERT INTO Orders VALUES (2, 3, '2023-07-16', 12, 2);

For OrdersDB2:

USE OrdersDB2;
INSERT INTO Orders VALUES (3, 2, '2023-07-15', 14, 1);
INSERT INTO Orders VALUES (4, 4, '2023-07-16', 10, 3);

To retrieve the data, you simply connect to the appropriate shard and execute your SELECT statements:

USE OrdersDB1;
USE OrdersDB2;

Results From the Queries:

For OrdersDB1

If you executed the following commands:

USE OrdersDB1;

You would get:

OrderId CustomerId OrderDate ProductId Quantity
1 1 2023-07-15 10 5
2 3 2023-07-16 12 2

For OrdersDB2

If you executed the following commands:

USE OrdersDB2;

You would get:

OrderId CustomerId OrderDate ProductId Quantity
3 2 2023-07-15 14 1
4 4 2023-07-16 10 3

In DbSchema:

DbSchema is a database designer that allows you to visually design & interact with your database schema. It doesn’t directly execute sharding, but it helps you in managing and visualizing your sharded databases.

Step 1: Connect to each database

Open DbSchema and click on Connect (the plug icon). Fill in your connection details for OrdersDB1, then repeat for OrdersDB2.

Step 2: Visualize your schema

Once connected, you can visualize your schema by clicking on Layout (the eye icon), then selecting New Layout. The Tables in your database will be listed on the left side. You can drag and drop them to the layout.

Step 3: Synchronize your schema

With sharding, you need to ensure that the schema of each shard matches. DbSchema has a Schema Synchronization feature that allows you to compare your databases and apply the changes needed to keep them synchronized.

Step 4: Query your data

In DbSchema, you can execute SQL queries to each database using the SQL Editor. Click on SQL Editor (the SQL icon), then type your query and press the Run button.

For example, to select all orders from OrdersDB1, your query would be:


You would then select OrdersDB1 from the dropdown list of databases and press the Run button.

With DbSchema, you can manage and visualize your sharded databases more easily, as well as ensure that their schemas are synchronized.

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.


Database sharding offers numerous benefits in performance, scalability, and availability, especially in managing extensive data sets. However, it requires thorough planning and technical knowledge. Understanding the concepts of sqlcmd and DbSchema can aid in the successful implementation of database sharding in SQL Server.


  1. Microsoft SQL Server Documentation
  2. Database Sharding
  3. DbSchema Documentation
  4. Sqlcmd utility

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.