Overview:

In SQL Server, indexing is a technique used to improve the performance of queries by reducing the amount of data that SQL Server needs to scan. You can think of it like a table of contents in a book—it helps SQL Server find data more quickly.

In this article, we will cover the following areas broadly:

• What indexes are

• Types of indexes

• How indexing is done in SSMS

• Example of creating and managing indexes

• Best practices and tips

Understanding index

An index is a data structure associated with a table or view that speeds up the retrieval of rows. It is created on one or more columns to provide a fast lookup mechanism.

Use of an index

Following are the main use of an index in database:

• Improve SELECT query performance

• Help in enforcing uniqueness (e.g., via unique indexes)

• Support primary key and foreign key constraints

• Reduce I/O and CPU time for large datasets

Different types of indices in SQL Server

Following are the different types of indices permissible in SQL Server:

• Clustered Index

  • Determines the physical order of data in a table.

  • A table can have only one clustered index.

  • Example: Often created automatically on primary key.

• Non-Clustered Index

  • Creates a separate structure from the data rows.

  • Can have multiple per table.

  • Good for searching, filtering, and joining on columns not covered by the clustered index.

• Unique Index

  • Ensures that no two rows have the same values in the indexed columns.

• Composite Index

  • Index on multiple columns.

• Full-Text Index

  • For performing full-text searches on large text fields.

• Filtered Index

  • An index with a WHERE clause—applies only to a subset of rows.

• XML and Spatial Indexes

  • For XML data types and geographical/spatial data types respectively.

Basic data setup for our practice

To practice our index creation, we will create some sample data across few tables. To setup data, run the following set of queries in your local database:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50),
    HireDate DATE
);

INSERT INTO Employee (EmployeeID, FirstName, LastName, Department, HireDate)
VALUES
(1, 'John', 'Smith', 'HR', '2018-01-15'),
(2, 'Jane', 'Doe', 'IT', '2019-03-22'),
(3, 'Michael', 'Brown', 'Finance', '2020-07-10'),
(4, 'Emily', 'Davis', 'IT', '2021-11-01'),
(5, 'Chris', 'Wilson', 'HR', '2022-05-12');

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
(1001, 1, '2023-01-10', 250.50),
(1002, 2, '2023-01-12', 100.00),
(1003, 1, '2023-02-15', 500.00),
(1004, 3, '2023-03-05', 330.75),
(1005, 2, '2023-03-20', 120.00);


CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100)
);


INSERT INTO Students (StudentID, FirstName, LastName, Email)
VALUES
(101, 'Alice', 'Morgan', 'alice.morgan@example.com'),
(102, 'Brian', 'Lee', 'brian.lee@example.com'),
(103, 'Carol', 'Kim', 'carol.kim@example.com'),
(104, 'David', 'Wright', 'david.wright@example.com');

Creating an index in SSMS

There are two main ways to create indexes in SSMS:

Using SSMS GUI

Steps to Create an Index Using SSMS GUI:

1. Open SQL Server Management Studio (SSMS).

2. Connect to your database server.

3. Navigate to your Database > Tables > [Your Table] > Indexes.

4. Right-click Indexes → New Index → Choose index type (e.g., Non-Clustered).

5. In the new window:

   • Name your index.

   • Click Add to select the column(s) to be indexed.

   • Optionally configure settings like Is Unique, Sort Order, Include Columns.

6. Click OK to create the index.

Using T-SQL Script

Creating a nun-clustered index:

CREATE NONCLUSTERED INDEX IX_Employee_LastName
ON Employee (LastName);

Creating a composite index with INCLUDE columns:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate
ON Orders (CustomerID, OrderDate)
INCLUDE (TotalAmount);

Creating a unique clustered index:

CREATE UNIQUE CLUSTERED INDEX IX_Student_ID
ON Students (StudentID);

Viewing indexes in SSMS

You can follow the following steps to view an existing index in SSMS:

• Go to Object Explorer > Tables > [Your Table] > Indexes
• You’ll see all indexes, including ones created automatically (like PK_ for primary key)

You can also use the following query to view the index:

SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Employee');

You should see a similar output:

Modifying and Dropping Indexes

Modify an Index

Indexes can't be directly altered. You must drop and recreate the index.

Drop an Index (Using T-SQL Script)

DROP INDEX IX_Employee_LastName ON Employee;

Drop an Index (Using SSMS GUI)

• Right-click on the index under the Indexes folder.

• Click Delete.

Once you run the query (or perform the delete operation from GUI), you should see one index removed upon running the query to view the index:

Index Performance Tips

• Use Database Tuning Advisor in SSMS to suggest indexes.
• Regularly reorganize or rebuild indexes to avoid fragmentation:

  ALTER INDEX IX_Employee_LastName ON Employee REBUILD;
  ​

• Don’t over-index—each index slows down INSERT, UPDATE, and DELETE operations.
• Use included columns to cover more queries without making the index too wide.

Best Practices

• Index columns used in WHERE, JOIN, ORDER BY clauses.
• Use filtered indexes for tables with a lot of NULLs or specific filters.
• Monitor index usage via:

  SELECT * FROM sys.dm_db_index_usage_stats;
  

• Create indexes during off-peak hours if rebuilding large indexes.
• Avoid redundant indexes.

Testing Performance with Indexing

Now, before we end the tutorial and wrap up the discussion, we will see a live example of how indexing helps in improving the performance. For that, first we will drop off any indexes we created on the Employee table earlier by using the following command:

DECLARE @TableName NVARCHAR(128) = 'Employee';
DECLARE @SchemaName NVARCHAR(128) = 'dbo';
DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL += 'DROP INDEX [' + i.name + '] ON [' + s.name + '].[' + t.name + '];' + CHAR(13)
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.type_desc <> 'HEAP'
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND t.name = @TableName
  AND s.name = @SchemaName;

-- Print the DROP statements
PRINT @SQL;

-- To actually drop the indexes, uncomment the line below:
-- EXEC sp_executesql @SQL;

Once executed successfully, all our indexes created on Employee table is now dropped.

Now, to test our indexing performance, we need to insert high volume of data to ensure that it mocks an actual client data volume. For that, we will run the following script to insert millions of data into the Employee table (which might take a couple of minutes to finish):

DECLARE @i INT = 1;
WHILE @i <= 1000000
BEGIN
    INSERT INTO Employee (EmployeeID, FirstName, LastName, Department, HireDate)
    VALUES (
        @i,
        'First' + CAST(@i AS NVARCHAR),
        CASE WHEN @i % 2 = 0 THEN 'Smith' ELSE 'Johnson' END,
        CASE WHEN @i % 3 = 0 THEN 'IT' WHEN @i % 3 = 1 THEN 'HR' ELSE 'Finance' END,
        DATEADD(DAY, -@i % 365, GETDATE())
    );

    SET @i = @i + 1;
END

Next, we will enable the execution time measurement metrics by running the following command:

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

Once done, we will run a select query on the Employee table with a where clause without any indexing setup:

-- Run query without index
SELECT *
FROM Employee
WHERE LastName = 'Smith';

Once run, go to the Messages tab in the below response section and check for the time elapsed to execute the query. You should see a similar output:

You can see that in our case; elapse time was 3227 milliseconds.

Now, we will introduce an index into the Employee table on the last name column to improve the search performance by running the following query:

CREATE NONCLUSTERED INDEX IX_Employee_LastName
ON Employee (LastName);

Next, run the same search query once again and check the elapse time information. You should see a similar output:

You can see that this time, the elapsed time is just 146 milliseconds, which is a huge improvement.

Conclusion

Indexing in SSMS is a powerful tool that can significantly improve the performance of your queries and overall database operations. Whether you use the graphical interface or T-SQL scripts, it’s important to understand how indexes work and when to use each type.

By combining thoughtful indexing strategies with regular monitoring and maintenance, you can ensure your SQL Server databases run efficiently and effectively.