Implementing Indexes - Microsoft SQL Server 2012 Performance Tuning - page 201

Till SQL Server 2005, 249 non-clustered indexes were allowed,
but after SQL Server 2008, including SQL Server 2012, 999
non-clustered indexes are allowed per table.
Unlike the clustered index, the non-clustered index stores the key column value along with the
row locator (pointer) to the actual data either in a clustered index, or in a heap if a clustered
index is not available.
Generally, making a unique key on any column could, by default, generates a non-clustered
index on that column. One row per non-clustered index is available in the sys.partitions
table with Index_ID>1 . The following is the T-SQL command that allows you to run a query
on the sys.partitions catalog view, to get information regarding non-clustered indexes
that exist in the database:
Select OBJECT_NAME(object_id)
AS TableName,* from sys.partitions
WHERE index_id>1
The following screenshot shows the result set generated by this query: