Identify Data Uniquely
Build databases with easier scalability by using SQL Server's uniqueidentifiers for your primary keys.
by Josef Finsel
With the growth of the programmable Web, more and more companies are spreading data out across multiple servers. They do this either to provide more scalable processing or to provide information locally rather than getting it all from a central location. As this occurs, old-fashioned, incremental identity columns are becoming less useful, especially if you can create new records on multiple servers for the same table. With this new paradigm, you need to step away from the integer and even the bigint data type available in SQL Server 2000 and embrace the uniqueidentifier.
SQL Server 2000, Also works with SQL Server 7.0
The uniqueidentifier data type is a 16-byte hexadecimal number that SQL Server generates randomly, and it's guaranteed to be unique not only within a table or database or even a server, but also across time and space. In this column, I'll explore the pros and cons of using uniqueidentifiers and how to implement them in an existing database.
Tables in SQL Server require primary keysone or more columns that can identify the data in that row uniquely. Tables might have naturally occurring primary keys, but sometimes you need to add a column whose sole purpose is to act as the unique identifier for that row of data. In many cases, this column is an integer data type that you also can define as an identity column. If you're using the Enterprise Manager to create the table, adding an identity column is as easy as checking the Identity checkbox for an integer column and filling in the values for the Identity Seed and Identity Increment. The Identity Seed defines the value for the first row entered, and the Identity Increment defines the interval between each new value. If you leave the defaults at 1 for each of these, the first row added to the table has a 1 in the column, the next has a 2, and so on.
This is a handy way of identifying the row of data uniquely, but it has a couple limitations. The real problem with using integers lies not in how much data they can handle, but in how SQL Server generates them. Although you can modify the Identity Seed and Identity Increment values, they still work on the basic principle of adding the increment value to the highest number that has been entered in the identity column. This method works fine if your data always exists in one database. If, on the other hand, you spread your data across multiple databases or multiple machines, you'll find quickly that the numeric identity value isn't unique.
Back to top