Edit

Share via

Create nonclustered indexes

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

You can create nonclustered indexes in SQL Server by using SQL Server Management Studio or Transact-SQL. 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 Database Engine 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 does not 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 is not 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 does not 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.

Using SQL Server Management Studio

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

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

Using Transact-SQL

To create a nonclustered index on a table using Transact-SQL

  1. In Object Explorer, connect to an instance of Database Engine with AdventureWorks2022 installed. You can download AdventureWorks2022 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