Skip to content

create-nonclustered-indexes.md

Latest commit

 

History

History
142 lines (88 loc) · 6.38 KB

create-nonclustered-indexes.md

File metadata and controls

142 lines (88 loc) · 6.38 KB
title Create Nonclustered Indexes
description This article shows you how to create nonclustered indexes by using SQL Server Management Studio or Transact-SQL.
author MikeRayMSFT
ms.author mikeray
ms.date 09/02/2025
ms.service sql
ms.subservice table-view-index
ms.topic how-to
ms.custom
ignite-2025
helpviewer_keywords
index creation [SQL Server], nonclustered indexes
nonclustered indexes [SQL Server], creating
nonclustered indexes [SQL Server], UNIQUE constraint
indexes [SQL Server], nonclustered
nonclustered indexes [SQL Server], PRIMARY KEY constraint
monikerRange =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

Create nonclustered indexes

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

You can create nonclustered indexes in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql]. A nonclustered index is an index structure separate from the data stored in a table that reorders one or more selected columns. Nonclustered indexes can often help you find data more quickly than searching the underlying table; queries can sometimes be answered entirely by the data in the nonclustered index, or the nonclustered index can point the [!INCLUDE ssDE] to the rows in the underlying table. Generally, nonclustered indexes are created to improve the performance of frequently used queries not covered by the clustered index or to locate rows in a table without a clustered index (called a heap). You can create multiple nonclustered indexes on a table or indexed view.

Before you begin

Typical implementations

Nonclustered indexes are implemented in the following ways:

  • UNIQUE constraints**

    When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table doesn't already exist. For more information, see Unique constraints and check constraints.

  • Index independent of a constraint

    By default, a nonclustered index is created if clustered isn't specified. The maximum number of nonclustered indexes that can be created per table is 999. This includes any indexes created by PRIMARY KEY or UNIQUE constraints, but doesn't include XML indexes.

  • Nonclustered index on an indexed view

    After a unique clustered index has been created on a view, nonclustered indexes can be created. For more information, see Create indexed views.

Security

Permissions

Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Use SQL Server Management Studio

Create a nonclustered index by using the Table Designer

  1. In Object Explorer, expand the database that contains the table on which you want to create a nonclustered index.

  2. Expand the Tables folder.

  3. Right-click the table on which you want to create a nonclustered index and select Design.

  4. Right-click on the column you want to create the nonclustered index on and select Indexes/Keys.

  5. In the Indexes/Keys dialog box, select Add.

  6. Select the new index in the Selected Primary/Unique Key or Index text box.

  7. In the grid, select Create as Clustered, and choose No from the dropdown list to the right of the property.

  8. Select Close.

  9. On the File menu, select Save table_name.

Create a nonclustered index by using Object Explorer

  1. In Object Explorer, expand the database that contains the table on which you want to create a nonclustered index.

  2. Expand the Tables folder.

  3. Expand the table on which you want to create a nonclustered index.

  4. Right-click the Indexes folder, point to New Index, and select Non-Clustered Index....

  5. In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

  6. Under Index key columns, select Add....

  7. In the Select Columns from table_name dialog box, select the check box or check boxes of the table column or columns to be added to the nonclustered index.

  8. Select OK.

  9. In the New Index dialog box, select OK.

Use Transact-SQL

Create a nonclustered index on a table using Transact-SQL

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE] with [!INCLUDE sssampledbobject-md] installed. You can download [!INCLUDE sssampledbobject-md] from sample databases.

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    USE AdventureWorks2022;
GO
-- Find an existing index named IX_ProductVendor_VendorID and delete it if found.
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
-- Create a nonclustered index called IX_ProductVendor_VendorID
-- on the Purchasing.ProductVendor table using the BusinessEntityID column.
CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID
    ON Purchasing.ProductVendor (BusinessEntityID);
GO

Related content