Hugo Kornelis (5/7/2010)
And if you never use the identity column in queries, then the best way to improve performance is to drop that unused column.
What if it's only there to provide a short UID for child tables to reference? You'd never use the column to access the row, but you'd read it to access the child rows.[/quote]
Then you'd be using the column in queries, right? And those queries would benefit from the index on the identity column.
In fact, even if you'd only use the IDENTITY for a FOREIGN KEY constraint and never use it to actually combine referenced and referencing row (which, in my eyes, would be a purely theoretical scenario), you'd still benefit from the index, as without it a table scan has to be used to enforce the FOREIGN KEY constraint.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis