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 |
|
kristian
Starting Member
21 Posts |
 Posted - 2002-06-30 : 17:55:26
|
| Ive just read robvolks "Identity and Primary Keys - Part I" tutorial that advises againts the use of the ID/int as primary key.Im currently redesigning my site and i had chosen to move my primary key on my customers table from the current varchar called username, to an Int. The reason being that i have read severel times that its more efficient selecting records by int rather than char, and seeing as most queries on the site will be relating to a user if figured int would be the best option.But reading this tutorial has thrown a spanner in the works. Primary key on int/id on varchar/username ?Im sure the performance gain outweights the reasons for not using them stated in the article |
|
|
joshb
Yak Posting Veteran
52 Posts |
Posted - 2002-06-30 : 22:32:22
|
| Not exactly sure on performance of selecting int vs varchar but you should also consider the additional overhead of using the int. First, you will need a unique constraint (and not null) on the username to avoid duplicates. Second, depending on the nature of your queries you may find yourself selecting more colums than necessary just because you used the int as your PK. My opinion is to use the natural PK and stick with username.Josh |
 |
|
|
kristian
Starting Member
21 Posts |
Posted - 2002-07-01 : 08:59:37
|
I just read this too that also kinda confuses the issue.quote:
As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases--such as an identity column, or some other column where the value is increasing--and is unique. In many cases, the primary key is the ideal column for a clustered index
|
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-01 : 09:16:27
|
| kristian, I would suggest you try both and test performance taking into consideration not just SELECTs, but you INSERT/UPDATE/DELETE dml as well. I think you'll find that the natural key is more meaningful, and that fact will outweigh any minimal (probably no percievable) indexing benefit. Books OnLine seems to advocate an IDENTITY as the PK and Clustered Index for most tables. I (and many many professionals) think IDENTITY is a crutch that bastardizes the relational model. Make your choices based on testing, rather than reading, as each implemenation will be effected by site specific business rules.<O> |
|
|
|
|
|
|
|
|
int of varchar primary key