|
Subject:
|
Use of UNIQUEIDENTIFIER
|
|
Posted By:
|
ggandta
|
Post Date:
|
3/8/2007 7:57:11 PM
|
G'Day Thearon,
Firstly I must heap the sunshine and praise on your book, your writing style is 10000% tailored the way I best learn and I powered through the first 13 chapters in a week. I have not looked at 14 - 16 yet and probably will not for a long time as they are not in the area that I am trying to learn just yet.
Hopefully this is not a stupid question and you will have a fairly simple answer. I am reviewing an existing project (modeled on an existing DOS application that I am trying to move out of antiquity and into today) and looking at my data design. In your book you made fairly heavy use of the UNIQUEIDENTIFIER data type and I was wondering why this was the case, given that it is 16 bytes and would have a significant impact on database size with a lot of transactions.
The project I am working on is very data driven and there are many internal relationships between the tables that utilise key unique values to bind this relationship. These are currently CHAR with lengths of 8 to 10 characters depending on their use and I was wondering if there was any significant advantage of UNIQUEIDENTIFIER over CHAR to handle this.
Hope you can help with your reasoning so I can make an informed decision on my database structures.
Graham Hillier
|
|
Reply By:
|
Thearon
|
Reply Date:
|
3/9/2007 5:31:25 AM
|
Graham,
Thank you very much for the praise and I'm glad my book is of use to you.
I've worked on various database applications over the years and have seen a lot of problems using an IDENTITY column as a primary key field for tables. Even when just an INTEGER data type is used for the primary key there is still a lot of potential for duplicate values to be entered. This is especially true in high transaction databases.
I find that a UNIQUEIDENTIFIER works very well as a duplicate GUID will never be generated. Using a GUID also sets up your database to be replicated down the line if the need arises. When working with replicated data, a UNIQUEIDENTIFIER must be used to unquiely identify each row of data.
I've not see a performance decrease using a UNIQUEIDENTIFIER and actually think it will offer better performance over a CHAR data type.
Thearon
|
|
Reply By:
|
ggandta
|
Reply Date:
|
3/9/2007 12:52:31 PM
|
G'Day again Thearon,
Thanks for the quick response. Those are areas I had not considered, Primary Key and Replication, but I suppose there is a distinct possibility that I might need Replication down the track. I will review my tables and use UNIQUEIDENTIFIER in them.
Thanks again,
Graham Hillier
|
