SQL Server Nonclustered indexes are a type of index that provide fast access to specific rows in a table, without determining the physical order of the data. Unlike clustered indexes, a table can have multiple nonclustered indexes.

Each nonclustered index includes a copy of the indexed columns and a reference, called a row locator, to the location of the corresponding row in the table. The row locator can be a clustered index key value or a unique identifier called a ROWID.

Nonclustered indexes are useful for optimizing queries that search for specific values in a column or columns, as well as for sorting and grouping data. To create a nonclustered index, you use the CREATE INDEX statement, and specify the column or columns that will be included in the index. You can also specify additional options such as sort order, fill factor, and the included columns.

Example

Here's an example of creating a nonclustered index in SQL Server:

CREATE NONCLUSTERED INDEX idx_last_name
ON employees (last_name, first_name);

In this example, we are creating a nonclustered index called "idx_last_name" on the "employees" table. The index is being created on the "last_name" and "first_name" columns.

This index would be useful if we frequently need to search for employees by their last name or if we need to sort the employees by their last name and first name. The nonclustered index stores the indexed data separately from the table data, which can improve query performance when searching or sorting by these columns.