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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-11-09 : 08:50:54
|
| James writes "I'm just a student working an internship and one of the guys in the team prefers to create tables with the primary key as Char 36, which he then populates with a generated GUID from VS (but without any of the {} around it). Now I don't like this and think that its not very good design but I don't know enough about performance etc to make a suggestion that I can back up.One thing that I don't like about using these CHAR(36) fields is that the data in the table is sorted as a string on this field since it is the primary key, so if I want it in another order (other than sorted on name) I'll have to add some other column to it.I suppose my question is, is it good practice to design a table with a primary key as Char and is there any difference with a char that just happens to always be a Guid and a correctly declared UniqueIdentifier field?CheersJames" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-09 : 09:06:55
|
| Well, you can go back to your teammate with the following:1) A GUID is 16-byte binary value. Storing it as varchar(36) wastes space and reduces processing efficiency. SQL Server can store it natively as a uniqueidentifier.2) Creating primary key constraints on GUID columns is OK, clustering on them is not. It will be a tremendous drain on insert activity because the table will need to do substantial manipulation on the clustered index. You need to find another column for the clustered index.3) You don't need to use a GUID as a primary key all the time. Like anything else, it's good in moderation, bad in excess. If the only reason to add a GUID to a table is to have a unique column, the table(s) probably has some deeper design issues that can cause problems later. If your teammate cannot fully explain why he is using a GUID, and why it's better than other column choices, he shouldn't be designing tables. Anything less than a full paragraph of logical reasons is suspect.You should take a look at this:http://www.datamodel.org/DataModelKeys.htmlIt has an excellent overview of the types of keys and their advantages/disadvantages. Read up on it, you should then be better able to make a case with your teammate. |
 |
|
|
|
|
|
CHAR vs Int or Guid as Primary Key