New Sequence Number in SQL Server 2005

SQL Server 2005 is loaded with features and in this article we will look at yet another function introduced in SQL Server 2005 . This function is called as NEWSEQUENTIALID().

Interestingly the debate over the newsgroups is always what if I don't want to use the IDENTITY function for a running value. The only other function that was available in SQL Server 2000 was the use of NEWID(). And that was to some degree a viable solution.

Let us look at some of the advantages and disadvantages of using one over the other. To start with the IDENTITY value, it is a norm in general to use this as a unique qualifier for a table's data. Even though most of us use it, in an OLTP database I presume this is a good option to have. But we need to understand that under heavy load we know that the last page is going to be under lot of stress because all the users are competing to get a space in the last page. This is under the assumption that the IDENTITY column is the PRIMARY KEY and CLUSTERED INDEX. So there can be hot spots on the HDD that can occur if we were to use a monolithically increasing number.

Now let us look at NEWID() version of the story. Firstly both these functions generate a unique identifier. But these are 16 byte and we would generally make this as the PRIMARY and CLUSTERED INDEX. And this very much contradicts one school of thought that says keep the key length as narrow as possible. Having said that when we were to use NEWID() as a key there was a problem of page splits because the values generated will be random. Unless the fill factor has been defined for the tables the page splits are a bigger problem as compared to the hot spots defined before.

Now what's new in SQL Server 2005, well even though these are my views over using one type of key over the other. But there are applications and users who still insist and want the best of both the worlds. And NEWSEQUENCETIALID() surely generates a GUID based on the machine's address. Even though this is unique it can be easily guessed. The algorithm is not random as in the case of NEWID(). A typical usage and output will be as below:

CREATE TABLE myGUIDTable (myCol uniqueidentifier DEFAULT NEWSEQUENTIALID()) 

INSERT INTO myGUIDTable Values (Default) 

GO 50 

Select * from myGUIDTable 

Interestingly this function can only be used as part of default value in the table definition. Hence from what I can see it is surely something that we can use it instead of IDENTITY.

Conclusion

Even though I am not trying to advocate in using this function, I would still let the decision stay in your hands. With plenty of new features introduced and the release getting closer, wait to hear more features discussed over our site.