Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
webappguru
Starting Member
10 Posts |
 Posted - 2009-07-02 : 12:49:42
|
| Hi SQL Gurus.Do tables with identity columns perform better than those that dont. I have a large db I am working with and I am finding that similiar size db's work better in performance when there is a identity column.Is there a reason why I should consider adding the identity column to existing tables... will they go faster??"I live for programming"www.aliabidhusain.net |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-07-02 : 13:32:25
|
| The identity columns do not affect performance. You may be seeing some indirect benefit, in that most people make the identity column the PK by default. This makes the table relatively free of fragmentation, unless a lot of deletes happen. the tables would have to get fairly large before you actually see a benefit from the lack of fragmentation. Most likely, you are getting better performance by using the identity column (which is indexed) in joins. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-02 : 13:35:50
|
| using identity column will make maintainence easier and also helps in maintaining uniqueness of values without much effort |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-03 : 22:28:12
|
| If you've modeled your data correctly you already have uniqueness and presumably indexes on PK/FKs. Performance difference should be minimal unless you have massive compound keys with low selectivity. |
|
|
|
|
|
|
Identity columns: do they help performance