February 27, 2003 at 9:05 am
I had a consult show me how he was using UniqueIdentifier instead of an Identity field using an INT. I have been using this for many of my tables. The obvious draw back is that the records can not be sorted using the Indentity column, but I really haven't found this to be an issue.
One issue I have run into is that ASP.NET will ignore the field at times because it's a GUID field.
Any comments on performance as my tables increase in size. The UniqueIdentifier field is larger, will that slow down indexing?
I'm curious as to what more experienced folks think. Thanks.
Tom
February 27, 2003 at 1:24 pm
Im in the minority, but I like uniqueidentifiers. One big reason is that I can generate the pkey on the client, not have to do an insert AND return the resulting key. The other is if you're merge or updating subscriber replication, using them keeps you out of the identity range managment business. Right now I have a mix of the two. I honestly could care less about sorting, almost always have a dateadded col with a default on the table.
Andy
February 27, 2003 at 1:31 pm
Andy, thanks for taking the time to reply. I had to change my thinking a bit when I started using them, but once I did, the sorting didn't matter much to me either. I also normally have a dateCreated field.
I'm gathering from your response that you do not see performance as an issue. The field is 4 times larger than an INT. I was thinking in terms of scalability.
BTW, what's a PKEY? Do you generate that in C or VB client side code? Most of the time on an insert I just default the field to newID(). If I need to know what the newID() was, I do it in a stored procedure and return the resulting newID().
February 27, 2003 at 1:49 pm
Pkey = primary key, sorry. Any time you make a col/row wider, it will have some impact on performance. If your uniqueidentifier is the clustered key, then it will have a somewhat great impact since that key is stored in all other indexes as well. I've done some rough testing, nothing good enough to really measure, it just didnt cost enough for me to worry about.
You can use newid, but instead you can call the Win api to generate the ID on the client - when that makes sense.
I've got an article up here on the site arguing my case in a little more detail.
Andy
February 28, 2003 at 11:00 am
I'd say it really depends a lot on your implementation. We have found the GUIDs to be really nice for configuration and lookup tables since the volume is low. We also have some fairly large tables that have huge penalties if we use GUIDs as identifiers because of the indexing issue. These tables have 30-100 million entries with hundreds of thousands being added each day. The GUID index is a major drawback here - especially if you make it the clustered index.
Just a thought.
Guarddata-
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply