Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I'm looking at using GUIDs (UUID) as primary key with clustered index in SQL Server.
In my webb-app I have many clients who will create items client side in javascript and save them only now and then.
The client code will create sequential GUIDs but items won't allways be saved straight away.
Am I loosing much of the benifit of using sequential GUIDs when the rows aren't saved in a perfect order?
Also, are there in the same way problems with occasionally mixing in normal random GUIDs?
Any GUID for an identity column will be an issue due to the size of it. This leaves less space in each page for actual data which means your data density is lowered and you are likely to see more page splits and fragmentation.
The problem when you use non sequential GUIDs on an id column with a clustered index is fragmentation. If you had 100 rows in the table with batches of 20 with sequential GUID values as the id and then wanted to add a random id value the engine may need to find space in the middle of your table to insert that record instead of just adding it to the end.
Depending on your fill factor level this could cause page splits which cause index fragmentation. Lower the fill factor of the index can help with fragmentation but not as much as always sequential values. This way each new record can be added to the newest page or a new page can be added at the end without the need to reorganize existing data.
Check here for more info.
