DbSchema Database Designer

DbSchema | SQL Server - How to Create an Index?

Publish on DbSchema Blog >>>

SQL Server alt >

Table of Contents


In the realm of __SQL Server, indexes play a crucialrole in enhancing theperformanceof database operations, particularlysearch queries. An index, much like an index in a book, helps SQL Server find data quickly and efficiently. This guide will explain indexes, their benefits, restrictions, types, and how to create them using sqlcmdandDbSchema`.


Before diving into creating indexes, ensure that you have the following:

  1. SQL Server installed and running.
  2. A basic understanding of SQL syntax.
  3. Familiarity with sqlcmd and DbSchema tools.

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

Understanding Indexes

An index in SQL is a data structure that improves the speed of data retrieval operations on a database table. Indexes are used to quickly locate data without needing to search every row in a database table each time a database table is accessed.

Advantages and Limitations of Using Indexes


Following are the advantages of using an index:

  1. Faster data retrieval: Indexes significantly speed up the data retrieval process in a database.
  2. Enhanced query performance: Indexes can vastly improve the performance of search queries.
  3. Effective sorting and grouping: Indexes assist in arranging data in a specific order and effectively implementing GROUP BY clauses.


Following are the limitations of using an index:

  1. Space requirement: Every index created requires additional space as it’s essentially a copy of the original table.
  2. Overhead on DML Operations: Indexes can slow down the speed of update and insert statements because each time data changes in a table, the index must be updated.
  3. Improper use can lead to issues: If not used correctly, indexes can lead to performance degradation and increased resource consumption.

Restrictions on Using Indexes

  1. No text, ntext, or image data types can be indexed.
  2. Computed columns that are not deterministic or precise cannot be indexed.
  3. SQL Server does not maintain statistics for non-key columns in a non-clustered index.

Permissions Required for Using Indexes

The user must have the ALTER permission on the table or view where the index will be created. If the index includes columns from other tables or views, the user must also have SELECT permission on those objects.

Types of Indexes

Type of Index Description _When to Use_
Clustered Only one per table, it sorts and stores data rows in the table or view based on their key values. When there is a column that is often sorted in order.
Non-Clustered Creates a logical order that does not match the physical order on disk. It contains a sorted set of pointers to the data. When you need to retrieve data without sorting the entire table.
Unique Ensures that the indexed column has unique values only. When you want to ensure the uniqueness of data in specific columns.
Columnstore Uses column-based data storage and query processing to achieve high data compression rates and query performance improvements. When working with large data warehouse fact tables.
Full-Text Allows fast text searching in character-based data. When performing complex word searches in character-based data.
Spatial Optimizes storage, retrieval, and manipulation of spatial data. When dealing with geometrical, geographical, or spatial data.

Creating an Index in sqlcmd

Step 1: Launch sqlcmd

Open your command prompt and type sqlcmd. Press enter.


Step 2: Connect to Your SQL Server

Next, you need to connect to your SQL Server. Use the following command to connect:

sqlcmd -S <your-server-name> -d <database_name> -U <username> -P <password>

Replace server name with your server’s name , username with your username and password with your password.

Step 3: Connect to Your Database

To connect to the TestDB database, you need to execute the following command:


After pressing enter, you’ll see a message that you’re now connected to the ‘TestDB’ database.

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

Step 4: Create the Index

Next, we’ll create an index on the FirstName and LastName columns of the Employees table. Use the following command:

CREATE INDEX idx_employee_name
ON Employees (FirstName, LastName);

The GO command is used to signal the end of a batch of Transact-SQL statements to the SQL Server utilities.

Sample Database:

Our sample database is named TestDB, and it has a table named Employees.

Employee Table:

The Employees table is structured as follows:

EmployeeId FirstName LastName Department _JoiningDate_
1 John Doe IT 2022-06-16
2 Jane Doe HR 2022-03-16
3 Mike Smith Sales 2022-02-01

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

Step 5: Verify the Index Creation

You can verify whether the index was created successfully by querying the system catalog view sys.indexes. Run the following command:

SELECT name AS IndexName, type_desc AS IndexType
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = 'Employees';

This query should return all indexes on the Employees table along with their types.

Result from the Query:

Following result will be obtained while executing the above query on our sample database:

_IndexName_ IndexType
idx_employee_name NON-CLUSTERED

In this table, PK_Employees is the primary key, which SQL Server automatically creates as a clustered index. The idx_employee_name is the non-clustered index we just created on the FirstName and LastName columns. The IndexType column indicates whether an index is clustered or non-clustered.

Creating an Index in DbSchema

DbSchema is a visual database designer that uses interactive diagrams. Here’s how to create an index using DbSchema.

  1. Open DbSchema and connect to your database.
  2. In the Schema panel, select the table where you want to add the index.
  3. Right-click on the table and select New Index.
  4. In the dialog box that appears, specify the index type and select the columns that you want to include in the index.
  5. Click OK.

The index will be created in the database, and it will also appear in the layout.

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.


Indexes are a vital part of SQL Server, offering quick and efficient data retrieval. However, they need to be used judiciously due to the associated space and performance considerations. Understanding the types of indexes and knowing when to use each one can significantly improve database performance. Both sqlcmd and DbSchema provide straightforward methods for creating indexes on your tables.


  1. Microsoft SQL Server Index Architecture and Design Guide
  2. DbSchema Documentation

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.