Newsequentialid (Histrory/Benefits and Implementation)


            In general, we made significant improvements in SQL Server scalability during Yukon.  One of the areas of improvement is replication scalability.  While doing merge replication testing we found out that scaling was severely affected by high number of I/O operations.  The cause of the problem was that new rows were inserted in random disk pages.  Guid generating function (newid) was returning non-sequential guids which resulted in random B-tree lookups.  After some investigation we figured out that we could use the new OS function UuidCreateSequential with some byte scrambling to convince the rest of SQL engine that guids are produced in sequential order.


           Therefore, we implemented the new intrinsic function, Newsequentialid which is nothing more than a wrapper over the OS function UuidCreateSequential.  Therefore all of the semantics of UuidCreateSequential are present in newsequentialid intrinsic.  The official documentation for UuidCreateSequential is here. 


            The conclusion:  If you are generating guids as your row identifiers, you may be interested in newsequentialid function.  For example, after replication folks started using this function they were able to completely fill their data and index pages.  Also, the row inserts did not require searches through the B-trees because the last modified page was already in memory. 


            You should be aware of all of the limitations of UuidCreateSequential before you start using newsequentialid.  For example, computers without network card will return only locally unique values and the IDs returned can be used to discover some network properties of your server. 


 


    Additional notes:


 


1)      While testing newsequentialid in highly concurrent environments, we found that on AMD64 machines the OS function may return same values.  Therefore, we put the global mutex so that only one user can be going through newsequentialid at the time. 


2)      The old guid generating function NewId is also a wrapper over an OS function CoCreateGuid, therefore all of the semantics of CoCreateGuid exist in NewId. 


3)      The only additional work that we do in newsequentialid is that we take guids and arrange bytes such that values work well with existing engine guid compare algorithm.  Since input bits are unique and we only move the bytes returned by the OS, the output is also guaranteed to be unique. 


4)      Because two consecutive calls to this function are guaranteed to return different results, the function is marked as non-deterministic.  Therefore, we opted to allow the use of this function only as default value.  The reason for this decision is because algebrizer and optimizer are limited in what they can do with non-deterministic functions. 

Comments (15)

  1. mobilebilly says:

    We are considering to use the OS function UuidCreateSequential to generate the key at the client side.

    As you said the implementation of the newsequentialid in Sql Server 2005 scramble the result from UuidCreateSequential, is there any issue with that approach?

    Is there any detail information about the scrambling?

  2. My current project uses GUID primary keys in a SQL Server 2005 database. The primary keys are created

  3. BSovers says:

    It is frustrating implementing NewSequentialID() as a default value within a table (though that is the ONLY place it CAN be used).  Every time I try it, SQL SMS keeps giving an "Error validating the default for column…" message each time I attempt to apply it.

  4. MorningZ says:

    Any luck ever getting around the validating error?

  5. Hugo Ribeiro says:

    A colleague pointed me to this code that implements NewSequentialId() in C#: http://codebetter.com/blogs/scott.bellware/archive/2006/12/27/156671.aspx

  6. zootie says:

    Scott Bellware’s c# implementation over on codebetter (link above) only calls the underlying OS function. However, SQL 2005’s implementation does some byte re-ordering after calling the same function, and if you want the sequential GUIDs to be "compatible", you’d have to do the same operations on the client side. You can see the reordering in the source code of yaflaGUID (http://www.yafla.com/dforbes/stories/2005/10/17/yaflaguid.html). You’d have to translate the assembly code to c#. In essence you call UuidCreateSequential (as Scott does), then invoke ToByteArray, swap bytes 0 & 3, 1 & 2, 4 & 5 and 6 & 7, and create a new GUID from the byte array.

  7. KCCE says:

    I posted a C# implementation that handles the byte reordering as a comment to Scott Bellware’s Blog post.

    Go to Scott’s blog entry (link above) and look in the comments for the c# snippet. This will produce a guid that is equivalent to SQL’s newSequentialID(). I did place the external definition to UuidCreateSequential() in a SafeNativeMethods class that is not included in the snippet.

    cheers.

    Kendall

  8. ShaneAKenyon says:

    The validation error seems to be a SQL Studio bug.  You can still save the table (after accepting the error _again_), using the function as the default value.  Thanks Scott and Kendall for your code – I will be working on a NHibernate generator for this and hopefully post it to my blog soon.

  9. ha-man says:

    Just an observation on the re-ordering of bytes in the implementation of newsequentialid. I understand fully why this must be performed to adhere to the ordering algorithm in SQL Server.

    But as I understand it this changes the algorithm version for generating the guid from 1 (which is what UuidCreateSequential uses), to whatever is currently the 2nd highest 4 bits of the time (currently D on my system).

    Am I correct in assuming that this is currently not a problem in practice because (i.e. it will not cause clashes with other algorithms because):

    1) There is no standardized algorithm which has been assigned D, E or F as version number

    2) The 2nd highest 4 bits of the time will not wrap around to a lower value any time soon.

    ?

  10. A few months ago we started development on a new system. From the ground up we redesigned everything

  11. Unraveling the mysteries of NewSequentialID

  12. creave.dk says:

    Sequential GUID i SQL Server og i .NET

  13. When creating client-side guids to be stored in SQL Server things are not as simple as you expect…