Find all Primary Keys With Non-Clustered Indexes

This T-SQL script is used to find all Primary Keys that have a non-clustered index associated with them. Ideally a primary key will use a clustered index which essentially means that the index is the table. This makes joins and queries that use the primary key much more efficient.

When using a non-clustered index as the primary key the table is stored as a heap or unordered table. New rows are simply added to the end of the table which can be the fastest way to load data. The problem arises when we need to search the data in a heap and after using the non-clustered primary key index we probably have to do a bookmark lookup in the table to find the rest of the row data. This is a two step operation compared to the one step index seek in a clustered primary key.

Other side affects of the heap structure include forwarded rows. When row updates happen and the data is too big to fit on the page any more the page is split. That is a second page is created and half the row data is moved to the second page. A forwarding record is added to the first page referencing the second page. This is much like file fragmentation and reading data from the table will now become much more random in nature (remembering random IO is around 50 times slower than sequential IO).

 -- Returns all non-clustered primary keys  
 select object_name(object_id) as [Table Name], i.name as [Index Name]  
 from sys.indexes i  
 where is_primary_key = 1  
 and type_desc <> 'CLUSTERED'  
 order by object_name(object_id) asc