Create nonclustered indexes
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
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
In Object Explorer, expand the database that contains the table on which you want to create a nonclustered index.
Expand the Tables folder.
Right-click the table on which you want to create a nonclustered index and select Design.
Right-click on the column you want to create the nonclustered index on and select Indexes/Keys.
In the Indexes/Keys dialog box, click Add.
Select the new index in the Selected Primary/Unique Key or Index text box.
In the grid, select Create as Clustered, and choose No from the drop-down list to the right of the property.
Select Close.
On the File menu, click Save table_name.
To create a nonclustered index by using Object Explorer
In Object Explorer, expand the database that contains the table on which you want to create a nonclustered index.
Expand the Tables folder.
Expand the table on which you want to create a nonclustered index.
Right-click the Indexes folder, point to New Index, and select Non-Clustered Index....
In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.
Under Index key columns, click Add....
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.
Select OK.
In the New Index dialog box, select OK.
Using Transact-SQL
To create a nonclustered index on a table using Transact-SQL
In Object Explorer, connect to an instance of Database Engine with
AdventureWorks2022
installed. You can downloadAdventureWorks2022
from sample databases.On the Standard bar, click New Query.
Copy and paste the following example into the query window and click 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