Esalter (11/11/2009)
If an identity column is used for the clustered index (CI) and NC indexes use the identity column (key) rather than a RID as the retrieval pointer then doesn't that mean that when the NC index is searched and the key (Identity column) is retrieved, it then needs to traverse the CI tree down to the leaf level to retrieve the actual row required as the 'identity column' alone wouldn't physically locate the row, it's just part of the data (leaf level of the CI)?
The identity column would identify the row (it's the clustering key). To get the rest of the row, SQL would do a lookup to the cluster. That's called a bookmark lookup in SQL 2000 and a key lookup in SQL 2005 and above. The physical location of the row is totally irrelevance.
That's discussed in this article. The section titled 'lookups'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability