June 26, 2009 at 8:51 am
Jules Bonnot (6/4/2009)
Thanks for your replies.i think i will write a little test and see how the heap with identiy performs against the CI with identity when under heavy inserts. I will post the script once i have done it.
Regarding the issue of primary keys. Obviously a PK is a form of constraint and must therefore involve involve some overhead to check.
Anyone had any bad experiences adding primary keys? What kind of impact can i expect?
The way I see it, while there is some overhead via PKs and FKs in inserts, updates and deletes, a lot of this really should be done anyway and you can't really get around such a thing in a relational database.
Whats the point of adding a PK to an identity column?
Thanks your help.
Jules
I've never tried adding a FK that referenced a non-PK column. Is that even possible?
June 29, 2009 at 2:44 am
Gordo (6/25/2009)
I don't know what the point is in having a primary key constraint on an identity column though (what would you gain from this?). Perhaps SQL Server is smart enough to NOT add any extra overhead when you do something redundant like that though, so maybe no harm done.
Below are the few advantages of adding PK to a identity col.
1. Uniqueness is maintained automatically i.e. Application doesn't need to generate unique value for each insert
2. Page splits don't happen as every new record will be inserted at the end of the table.
3. Size of non-clustered indexes are low as they store the clustered index key at the leaf level.
June 30, 2009 at 8:53 am
GSquared (6/3/2009)
An example of a bad clustered index is one on a GUID column that uses newid() instead of newsequentialid(), or one on a varchar column with people's names in it or something like that.
You know, that's kind of amusing... I had to give a lecture about that to some developers just last week. Spent a while trying to fathom why a small data set (30,000 records) of client activities was taking 40 seconds to query an index.
Looked at the database... had a nice identity column... then I noticed the clustered index was not on the identity column, but instead on the name of the contact that made the call that triggered the activity.
If the developer who created that particular table hadn't already left the company... I might have regretted my actions.
June 30, 2009 at 9:09 am
mtassin (6/30/2009)
GSquared (6/3/2009)
An example of a bad clustered index is one on a GUID column that uses newid() instead of newsequentialid(), or one on a varchar column with people's names in it or something like that.You know, that's kind of amusing... I had to give a lecture about that to some developers just last week. Spent a while trying to fathom why a small data set (30,000 records) of client activities was taking 40 seconds to query an index.
Looked at the database... had a nice identity column... then I noticed the clustered index was not on the identity column, but instead on the name of the contact that made the call that triggered the activity.
If the developer who created that particular table hadn't already left the company... I might have regretted my actions.
Ouch!
June 30, 2009 at 9:18 am
ta.bu.shi.da.yu (6/26/2009)
...
I've never tried adding a FK that referenced a non-PK column. Is that even possible?
You can add a FK as long as the columns that you reference have a unique constraint or a unique index.
I have had occasion to do this, but it is not a common thing to do.
You can read about situations where this might be useful in this blog post and the follow-up comments.
http://weblogs.sqlteam.com/jeffs/archive/2008/08/13/examining-database-primary-keys.aspx
June 30, 2009 at 11:38 am
These reasons are not the result of a PK per se, they are the advantages of a unique clustered index (which is built by default when you declare the PK). If for whatever reason you didn't want the overhead of constraint checking or the overhead that could be associated with the clustered index, then you wouldn't want to declare a PK. That might be the case with a table that has >10 million rows where the primary operation is inserts only. Although I always like to test everything. If the primary key isn't significantly impacting performance, you should keep it. Otherwise I'd try a clustered index, and then no index. The final conclusion will depend on the particular configuration (software and hardware) and how the database is being used exactly.
Ramji Below are the few advantages of adding PK to a identity col.
1. Uniqueness is maintained automatically i.e. Application doesn't need to generate unique value for each insert
2. Page splits don't happen as every new record will be inserted at the end of the table.
3. Size of non-clustered indexes are low as they store the clustered index key at the leaf level.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply