SQL Server Performance Forum – Threads Archive

Performance Difference Between Primary and Index

Hello fellowmates Is there is any performance difference between the primary key and index created to the primary key.
Thanks in advance.
Soma

Refer to this link http://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=9208]and this SQLTeam link http://sqlteam.com/item.asp?ItemID=2599] _________
Satya SKJ

quote:Originally posted by satya Refer to this link http://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=9208]and this SQLTeam link http://sqlteam.com/item.asp?ItemID=2599] _________
Satya SKJ

Thanks Satya Soma

Interested in the general concensus on this article you pasted referring to avoiding identity as primary keys.
http://sqlteam.com/item.asp?ItemID=2599 He argues a very (IMO) biased argument against using identity as PK, and I think its a touch naive too. In his example he was recommending (at least this was my interpretation) using the lan as the primary key. Fine its unique. In my opinion the problem with using data with meaning, as the primary key, is that if it ever needs to change (login names sometimes will change, think of a woman marrying and assuming a new surname etc), you then need to update all foriegn keys using this data. Fine, you can do this using cascaded updates, but at what cost. My argument would be that using a primary key *without* meaning is not such a bad thing. If it has no meaning, it will never need to change. And so any relationships based on this identity do not have to be patched up like in his example. I think if some data has meaning, but happens to be an ID of some form, that will noit change (employeeID etc), then use that, but otherwise use an identity. Interested in peoples opinions… Thanks

I found this comparison interesting:http://www.teradatamagazine.com/articles/1Q_2003/webonly/web4.htm Look at question #2.
I personally use identity columns quite a bit in my database designs. I don’t always replace natural keys with surrogate keys, but often the data I work with does not alway lend itself to a small, unchanging, guaranteed unique natural key. If I have a feeling that a natural key might change over time, I will usually add a surrogate key and put a unique contraint on what would have been the natural key. It just seems much easier that doing a lot of cascading updates if a key changes.

This article http://sqlteam.com/item.asp?ItemID=2599) is one of the worst piece of jibberish I have ever seen posted on a database related website. I don’t think the auther understands the difference between a Primary Key a Natural Key or an Identity column. Ignore, Ignore!
Joe E O

I wouldn’t go so far as to say it’s the worst article I have ever seen, but I do disagree with some of what the author says in it. I think he means well in trying to tell people that often times there is a really good natural key, and there are reasons to use them, however he seems to go a bit overboard on this concept. The example he gives with the employee table is pretty weak. One of the biggest problems I see with these articles is that the authors often forget that the database is not the user interface, and there is a lot code between the DB and the end user. This code is in place to handle all the data work, all the user should really ever see is the information that applies to them. And if you really want some good reasons to use surrogate keys, read some articles about data warehousing. Without surrogate keys most data warehouse applications would lose data integrity every time new data was loaded.

Yeah I’d go with Chappy and Negative on this, I use identity columns quite a lot to act a keys. People seem to forget that the world of applications and data is not always straightforward. I’ve migrated quite a few legacy databases (mainframe and such) to sql server and in the process redesigned tables and in alot of the cases the best solutions requires some normalisation and identity columns are a real blessing in this instance, lighten the amount of data in tables and do give you flexibility.
From a purists point of view I can see what they’re saying but it’s the real world out there with hideous database designs lurking here that need to be fixed and requirements and data that don’t always lend themselves to perfect designs. Cheers
Shaun World Domination Through Superior Software

Why is it the worst article? Most of the inforamtion is at least partially wrong…. 1) They’re not standard SQL. Most products have it but there’s no consistent implementation. Sybase and Sql Server have identity properties, Oracle has Sequences, I am pretty sure mySQL has sequences, I am pretty sure DB2 has an autonumber datatype. That is what percentage of the RDBMS market share, 80% – 90%? The point is if you a buy a RDBMS one of important features is the ability to generate a synthetic primary key in a fast scalable manner. Using a table that you increament is not a viable solution. It does not scale.
2) They can’t be updated. This violates the relational data model (not fatal, but not good either). Duplicates can be accidentally inserted (fatal). Primary Keys are immutable. That is good. This statement leads me to believe he does not understand the difference between a Identity column and a Primary Key. 3) They only create numeric values. GUID/NewID() are also numeric only, and are hard to read. I can’t even fathom "readability" as a plus or minus when thinking about table design. Readabilty?
4) Numeric values are not meaningful in many tables, and adding them complicates relationships between other tables. Wrong, Wrong, wrong. The fact that they have no meaning makes them ideal as a primary key. People change telephone numbers, social security number change – a synthetic key will never change.
Synthetic Primary keys also simplify relationships. You mean to tell me that is is easier to understand a three column foreign key than a one column foreign key….
1) Primary Keys are supposed to be immutable. This is always the case with purely synthetic keys generated by a Oracle Sequence and SQL Server Identity column. 2) Would it be cofusing to

]]>