Welcome Guest!
Create Account | Login
Locator+ Code:

FTPOnline Channels Conferences Resources Hot Topics Partner Sites Magazines About FTP RSS 2.0 Feed

Click here to receive your FREE subscription to Visual Studio Magazine

email article
printer friendly
get the code
more resources

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.

Technology Toolbox
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 keys—one 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

Printer-Friendly Version

Java Pro | Visual Studio Magazine | Windows Server System Magazine
.NET Magazine | Enterprise Architect | XML & Web Services Magazine
VSLive! | Thunder Lizard Events | Discussions | Newsletters | FTPOnline Home