Think About Replacing Uniqueidentifier with Sequence in Denali

As I’ve finally been finding some time recently to play with CTP 1 of SQL Server Denali, I’ve focused a lot of my energy around the Sequence object. This is because I have an Oracle PL/SQL background and used Sequences quite a bit in the old days there.

When talking to SQL Server DBAs and developers about Sequences, the conversation often starts with “this is pretty cool” and something that seems interesting on a forward-looking basis. I mean, in reality, who wants to go back into code and data models and make changes to tables, right? So is there a good case to use the Sequence to replace a table-based ID field like an Identity column or uniqueidentifier?

So far, I’m thinking that uniqueidentifier will be something that you’ll want to look at. The 2 most common constructs in T-SQL for creating ID fields in tables is to use either a uniqueidentifier as the column data type or use an INT with the identity property. The identity property in current SQL Server versions is probably the closest correlation to the new Denali sequence object. But the sequence object nicely encapsulates the IDs away from being tied to any single table or object. I describe this usage futher here.

Certainly you can go back and replace your IDENTITY columns easily with sequence objects because the INT data type is a natural fit for sequence #s. However, removing the identity property is not easy. Threre is no ALTER statement to do it nice & easy for you. And even though the SSMS GUI supports removing the identity property from a table column specification, the T-SQL that is generated from that action is actually the creation of a COPY of your table and data, drop the original table and re-create a new table without the identity definition! That’s not fun and not good, especially if you have a very large table.

Replacing the GUID field in a UNIQUEIDENTIFIER is not easy, either. You will want to remove that field and replace it with an INT using sequence. But this can only be done if you do not have that field as a foreign key in other tables. However, if you can replace it, do so, especially if you are using the GUID for these purposes: because it has guaranteed uniqueness and you are using it as a clustered index key.

Sequence will be a better option in those instances. The Sequence object has its own T-SQL to manage the #s being fed into your INT field to help with uniqueness when moving the data between instances and the sequential INT value of sequence is a MUCH better clustered key than a GUID. The control that you can have over the sequence separate from the table definition should help eliminate some of the limitations that may have led you to use the uniqueidentifier.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s