2

We are using GUID as primary key (we know it's not good option, but can not change now). So as all know our indexes get fragmented very quickly. Another good option to replace the GUID by Sequential ID. To do this, Code change is like:

Old Code :

ObjectName.Id = Guid.NewGuid();

New Code :

ObjectName.Id = Sequential.NewGuid();

Here Sequential is our static class, which creates the Sequential GUID using "rpcrt4.dll". But our tests shows that this also does not work well with the indexes, and they get fragmented.

Another interesting finding is that, if we save this sequential GUID as "String" in the database then our indexes are not getting fragmented.

Now I have following doubts/queries:

  1. Why Server is behaving differently when we are saving the same string as "String" and "GUID"? As per my understanding till now, internally it saves everything as String.

  2. Is there any way to configure the database to tell that, consider our GUID as string and treat them equally?

Here are some details of environment:

  • Database: SQLExpress
  • Coding language: C#
  • Can not rely on Server to generate the key, we have to set the key from code itself.

Even if not exact solutions, pointers to the solution are also welcome.

PM.
  • 1,735
  • 1
  • 30
  • 38

2 Answers2

1

A guid is not stored as a string. It is stored as 16 bytes of data - the characters in the guid are a hexadecimal representation of the bytes, where the first pair is the least significant value.

When RPCRT4 generates sequential GUIDs, it seems to treat byte 4 as the least significant. That may be the reason that your indexes are becoming fragmented.

Despite your claims to the contrary, I would recommend using SQL Server's NewSequentialID function.

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • Thanks for the explanation about the difference, but still the issue is same that I can not let the SQL Server give the ID. I want to know about some configuration in SQL Server itself.. – PM. Oct 18 '13 at 09:48
0

Btw. - do you generate your sequential guids on large number of computers? As SQL server primarily sorts by the portion of guid specific for the source machine (details in answer to this question - which might have some valuable information for you: Sequential GUIDs). In that case you might get some results by converting the Guid to byte array, swap it's portions and then recreate the Guid.

Another variation of this approach might be replacing the machine identification part of guid with always same static value and use different portions of Guid (but not the timestamp specific portions!) to differentiate between machines - but this would work only if you'd have very small number of machines where you need to generate guids.

Community
  • 1
  • 1
Jan
  • 1,905
  • 17
  • 41