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.
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.
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
15 People are following this question.
Is there any way to speed up dropping a user when they have a large number of tables etc?
Find out which client IP and Query using highest network Bandwidth
Generate a specified number of UIDs in SSRS or any other suggested tool
How do I port a SqlServer application that uses IDENTITY columns to oracle?
Why does the identity value start with 2 in 2008 R2 and with 1 in 2012?