Sean Grebey (7/2/2013)
Sure I understand that indexes are against tables. And I understand the difference between clustered and non-clustered indexes. My question is will non-clustered indexes perform better if there is a clustered index on the table. That is will they used the clustered key perform better if one exists?
They absolutely can (it always depends) especially if the clustered index is unique. The columns of a clustered index are added to every non-clustered index. If there is no unique clustered index, then an 8 byte "uniquefier" is also added to the index. Same holds true for when a non-unique clustered index is used except it's worse. In such cases, the columns of the clustered index are added to the columns of the non-clustered index and then the 8 byte uniquifier is added to that which could make a single column clustered index quite wide and comparatively slow.
One of the best presentations I've found on the subject can be found in the "movie" at the following URL.
http://technet.microsoft.com/en-US/sqlserver/gg508879.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.