Index : index_name

Like the previous statement with the clustered index, this statement indicates that the optimizer used an index to retrieve rows instead of scanning a table. The index_name that follows the Index : label is always the name of a nonclustered index on the table. Remember that each table can have no more than one clustered index, but each can have as many as 249 nonclustered indexes.

The following query illustrates the use of a nonclustered index to find and return rows. This query uses the sysobjects table in the master database as an example, rather than a table in pubs. (Using a nonclustered index on the pubs tables is generally more costly in terms of I/O than a straight table scan, because most of the tables are only one page in size.)

Query:

SELECT *
FROM master..sysobjects
WHERE name = 'mytable'
AND uid = 5

SHOWPLAN:

STEP 1
The type of query is SELECT
FROM TABLE 
master..sysobjects
Nested iteration
Index : ncsysobjects