question

rheynen avatar image
rheynen asked

Does having an Identity column speed up SQL Server

Does having an Identity column speed up SQL Server...I heard awhile back that it helps SQL speed up queries to have an int identity column, even if it isn't used.

identity-columnspeedup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered

Never heard of that and it’s not necessarily true.

Good database design, good index design, good query writing, good hardware, good maintenance routines and you’re on to a winner.

Now if the table can benefit from an identity possibly as a leading key in an index then yeah it will help performance. But generally identities don’t enforce the business logic so they generally are not the best candidates for clustered keys. Maybe a non clustered. But as with all things SQL it is an “It Depends”.

Having an identity on its own isn’t going to speed up but mix that in with a lot of other constructs and yeah you could see some benefit.

It Depends

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.