More on Primary Keys and GUIDs - NewSequentialID()

In answering a query on the SQLDownUnder mailing list today, one of the functions Paul Nielsen mentioned was the NewSequentialID() function, which is new to SQL Server 2005. I then had an email asking about this function, so I thought a short post explaining it might help.

In a previous post, I discussed one of the problems identified with using GUIDs as primary keys where they end up being used in clustered indexes. The preference for clustering keys today is short and monotonically increasing. Many will now consider a GUID as "short enough" so the remaining problem is the need to have a value that's continually increasing, so that reasonable insert performance is achieved. This is what the NewSequentialID() function is designed to provide.

The function used to calculate a GUID has been well thought out. As soon as you start to change how that works, you have to start to worry about the real "randomness" of the calculated value. NewSequentialID() isn't an answer to every problem but it might help in certain situations.

One of the internal input parameters to the function is the MAC address of the computer's network card. The NewSequentialID() function generates a GUID that is based on this MAC address. Each value provided is always larger than any other value generated on the same computer. The issues to consider with this are:

1. MAC addresses are meant to be globally unique also. There have been situations where they haven't been eg: network card manufacturers reusing address ranges. For the network card manufacturers, that isn't an issue unless two cards with the same address end up on the same physical network segment. Overall, I'd rate the likelihood of this being a problem as low.

2. Some computers don't have network cards. Where this occurs, the values generated could well end up duplicated on another system. This means, the generated GUIDs are fine on that system but combining data from multiple systems could yield surprises :-) Again, this is unlikely to be an issue except in very restricted situations ie: most computers running SQL Server have network cards of some type.

3. The GUIDs generated by NewSequentialID() follow a pattern. There may be a security issue to consider with this as knowledge of recently generated values could allow someone to predict upcoming values. There are limited scenarios where this would matter but in those situations, it could well be a showstopper.

4. As mabster mentions in the comments, one of the main reasons for using GUIDs as primary keys is that they can be generated at the client without reference to the database. Additionally, the NewSequentialID() function can only be used as a column default.

Apart from these, NewSequentialID() looks to be another useful new addition to the toolset although useful only in specific scenarios.