Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

Using IDENTITY as a key column Expand / Collapse
Author
Message
Posted Friday, May 7, 2010 1:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 5, 2010 10:56 PM
Points: 230, Visits: 17
Index on identity column improves performance
Post #917693
Posted Friday, May 7, 2010 2:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:55 AM
Points: 1,813, Visits: 6,586
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?
Post #917721
Posted Friday, May 7, 2010 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 6,098, Visits: 8,367
Toreador (5/7/2010)
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?


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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #917754
Posted Friday, May 7, 2010 3:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:55 AM
Points: 1,813, Visits: 6,586
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!

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.
Post #917759
Posted Friday, May 7, 2010 4:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:21 AM
Points: 6,098, Visits: 8,367
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
Post #917764
Posted Saturday, October 2, 2010 5:26 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 11:01 AM
Points: 703, Visits: 327
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.
Post #997253
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse