Hugo Kornelis (5/7/2010)
If you define the identity column as a PRIMARY KEY, then there always is an index. SQL Server automaticallly creates an index when you create a PRIMARY KEY constraint, and uses that index to enforce the constraint.
D'oh! :ermm:
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.