Int Identity VS Uniqueidentifier
-
A coworker and I just had a excellent discussion about the pros and cons of using GUID as the primary key for a table VS the use of an INT IDENTITY.
We work in a financial enviroment were we cannot pass the primary keys (int identity) around on web applications because they can be easily manipulated through the Query String. So when we need to track something, say a user, we use a GUID for that. We end up with GUID and an INT IDENTITY for every user.
The original design was done this way so that the indexes would be faster and so that we wouldn't have to large foreign keys.
Do you think that changing the INT IDENTITY to a UNIQUEIDNETIFIER would have a huge impact on a database of less than 1 million rows?
Any general comments about this are welcome.
-
You will find equal number of people supporting both side of the equation. Coming to your question "would have a huge impact on a database of less than 1 million rows?" I will say answer is No. I have not done any bench mark on this but I feel it will not have a huge effect in terms of performance. You might get a different answer in the thread. Just do a quick benchmark and post your results too.
Kindest Regards,Amit Lohia
-
If the GUID is the primary key and you are doing OLTP, it could have a large impact. Since GUIDs are random, rather than ordered in creation like an identity, inserts will frequently occur "in the middle" rather than always at the end. You can cause a serious increase in page splitting.
On the other hand, if the data is more read only in nature, you probably would not notice much of a difference.
Given the security you are looking for the page splitting impact may be inconsequential. I've gone down that same road with security control and user session management with SQL and can still sustain much more data traffic than web server traffic.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson -
You may wish to split the difference and look into NEWSEQUENTIALID(), which is a GUID-like function which generates machine-specific sortable guids. It is important to note that like an identity, it is possible to guess the next value returned by the function.
-
The page splitting will only occur if you have the uniqueidentifier as a clustered index. A Primary Key does not necessarily need to be a clustered index. That's just the default behaviour.
So a non-clustered primary key constraint will not be the cause of any excessive page splitting.
--------------------
Colt 45 - the original point and click interface
-
True, but how many times do people use the default - Primary Key, Clustered Indexed GUIDs are an extremely bad idea!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply