May 7, 2010 at 1:54 am
Index on identity column improves performance
May 7, 2010 at 2:54 am
vignesh 38804 (5/7/2010)
Index on identity column improves performance
If you never use the identity column in queries but only use it as a primary key then doesn't it actually make performance slightly worse?
May 7, 2010 at 3:50 am
Toreador (5/7/2010)
vignesh 38804 (5/7/2010)
Index on identity column improves performanceIf you never use the identity column in queries but only use it as a primary key then doesn't it actually make performance slightly worse?
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.
And if you never use the identity column in queries, then the best way to improve performance is to drop that unused column.
May 7, 2010 at 3:58 am
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.
May 7, 2010 at 4:06 am
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.
October 2, 2010 at 5:26 pm
I agree. I'm somewhat new to this site, but poorly worded questions sometimes take more thought to deduce the writer's meaning than just answering or researching the material... 🙂 I see that even some of the long-timers are getting burned by them occasionally.
April 18, 2016 at 7:25 am
Doing research on identity, thanks for the help.
Viewing 7 posts - 61 through 66 (of 66 total)
You must be logged in to reply to this topic. Login to reply