Using NewSequentialID Instead of NewID

SQL Server 2005 provides a new function called NewSequentialID that's similar to NewID(), a function that originated in SQL Server 2000 and is supported in SQL Server 2005. Both functions return a value of data type uniqueidentifier.

In the past, many people used NewID() to assign primary keys to columns, and these columns were unique across servers. When SQL Server 2000 first came out, I too used NewID() to create primary keys. Alas, I soon determined (as did many others) that using the NewID() function this way can cause a variety of performance problems and that NewID() doesn't provide a unique value that increases in order, as the IDENTITY-based key does.You get some important performance benefits when a key value is always higher than the last assigned value; generally these benefits are related to how data is stored on the pages.

The NewSequentialId creates a globally unique identifier (GUID) that's always greater than any GUID previously generated by the NewSequentialId function on a specified computer. Let's look at the example in Listing 1, which shows how to use the two functions to assign GUIDs that are higher in sort order than the previously assigned values.

Figures 1 and 2 show the results of queries that were generated with the NewSequentialID and the NewID() functions. Notice that the returned rows in Figure 1 are ordered by the NewSequentialIDCol and the rows in Figure 2 are ordered by NewIDCol.

We know that the rows were logically ordered according to the identity data type in the RowNumber column. When you look at Figures 1 and 2, you can see that when the query performs ORDER BY NewSequentialIdCol, the rows are returned in the same order as the RowNumber column. If you're comfortable reading hexadecimal, you'll see that the GUID values of NewSequentialIdCol are assigned in increasing order and the values for NewIdCol aren't.

I can't discuss all the ins and outs of the performance benefits of assigning the GUID values in increasing order, but consider this example. Say you have a table called Lots Of Data with 1 million rows and you use NewID() to define a clustered primary key. If you insert 1 million new records into that table you don't know where the rows will be put.The clustered index ensures that the rows will be inserted in order of the clustering key, but the NewID() function doesn't provide predictable ordering.

However, if you use the NewSequentialId() function, you'll know that the 1 million rows will be inserted in sequential order at the end of the table.

In some cases, it might be beneficial to sprinkle the inserts across the data pages that the table uses. However, in many cases, it's desirable to know that the new records were added at the end of the table.