MS SQL Database with clustered GUID PKs - switch clustered index or switch to sequential (comb) GUID

Posted by Eyvind on Stack Overflow See other posts from Stack Overflow or by Eyvind
Published on 2010-04-09T08:51:33Z Indexed on 2010/04/09 8:53 UTC
Read the original article Hit count: 565

Filed under:

guid

|

uniqueidentifier

|

clustered-index

|

sql-server

We have a database in which all the PKs are GUIDs, and most of the PKs are also the clustered index for the table. We know that this is bad (due to the random nature of GUIDs). So, it seems there are basically two options here (short of throwing out GUIDs as PKs altogether, which we cannot do (at least not at this time)).

We could change the GUID generation algorithm to e.g. the one that NHibernate uses, as detailed in this post, or
we could, for the tables that are under the heaviest use, change to a different clustered index, e.g. an IDENTITY column, and keep the "random" GUIDs as PKs.

Is it possible to give any general recommendations in such a scenario?

The application in question has 500+ tables, the largest one presently at about 1,5 million rows, a few tables around 500 000 rows, and the rest significantly lower (most of them well below 10K).

Furthermore, the application is installed at several customer sites already, so we have to take any possible negative effects for existing customer into consideration.

Thanks!

© Stack Overflow or respective owner

Related posts about guid

MS SQL Database with clustered GUID PKs - switch clustered index or switch to sequential (comb) GUID

as seen on Stack Overflow - Search for 'Stack Overflow'
We have a database in which all the PKs are GUIDs, and most of the PKs are also the clustered index for the table. We know that this is bad (due to the random nature of GUIDs). So, it seems there are basically two options here (short of throwing out GUIDs as PKs altogether, which we cannot do (at… >>> More
Nhibernate Guid with PK MySQL

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello colleagues. I've got a question. I use NHibernate with MySql. At my entities I use Id(PK) for my business-logic usage and Guid(for replication). So my BaseDomain: public class BaseDomain { public virtual int Id { get; set; } public virtual Guid Guid { get; set; } public class… >>> More
Please for efficient query

as seen on Stack Overflow - Search for 'Stack Overflow'
I want to have efficient query to get some rows from my table. Here is I think the best presentation of my table. -Somedate is not duplicated - it is date of modifiedon -a,b,c are parent ids, let say countryCode -1,2,3,4 are subparent, let say citycode -guids are id of rows -true, false are values… >>> More
... i just avoid GUID

as seen on SQL Blogcasts - Search for 'SQL Blogcasts'
Our partner was explaining to me that they are using GUID as primary key on all the tables. My immediate reaction was - why? and couple of basic doubts were: - since I can read uniqueidentifier, it does not tell me absolutely anything - if I will use my relational table, i sure will use other columns… >>> More
Convert string to GUID with sscanf

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm trying to convert a string to GUID with sscanf: GUID guid; sscanf( "11111111-2222-3333-4455-667788995511", "%08x-%04x-%04x-%02x%02x-%02x%02x%02x%02x%02x%02x", &guid.Data1, &guid.Data2, &guid.Data3, &guid.Data4[0], &guid.Data4[1], &guid.Data4[2], … >>> More

Related posts about uniqueidentifier

ASP.NET Conversion failed when converting from a character string to uniqueidentifier

as seen on Stack Overflow - Search for 'Stack Overflow'
hey all, i have a table which has a primary key (UserID) this is of type UniqueIdentifier. im trying to insert a value into this field but i keep getting an error. i want to get the userID of the current user and insert the into the user_Details table, but i keep getting this error Conversion… >>> More
From varchar(36) to UNIQUEIDENTIFIER

as seen on Stack Overflow - Search for 'Stack Overflow'
I am trying to cast an AuctionId that is a UNIQUEIDENTIFIER to an varchar(36) and then back to an UNIQUEIDENTIFIER. Please help me. CAST((SUBSTRING(CAST([AuctionId] as VARCHAR(36)), 0, 35) + '1') AS UNIQUEIDENTIFIER) But I keep getting this error: Msg 8169, Level 16, State 2, Line 647 Conversion… >>> More
sql server change PK type from int to uniqueidentifier

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi! I need to change the type of my primary key column on a table from int to guid. The database already has data that I don't want to lose, and there are foreign keys to consider. Is there a painless way to do this or do I have to manually do it through a big a** script?:) I would appreciate any… >>> More
How to pass null for a parameter in stored procedure - the parameter is uniqueidentifier

as seen on Stack Overflow - Search for 'Stack Overflow'
In C# code I have a variable Guid? name.But,how to initialize this parameter to null,since I cannot assign null for a guid type >>> More
Isn't INT more efficient than UNIQUEIDENTIFIER?

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a parent table and child table where the columns that join them together are the UNIQUEIDENTIFIER type. The child table has a clustered index on the column that joins it to the parent table (its PK, which is also clustered). I have created a copy of both of these tables but changed the relationship… >>> More