Generating a unique number for each row

T-SQL developers know that using newId() generates new GUID which is guaranteed to be unique each time the function is used.

The other day, in my work I was in a situtation where I need to generate unique numbers for every row selected or inserted. One way of handling it is to create a temp table (create table #tablename) or a TABLE type with a field of type int with IDENTITY (1,1). This will accomplish what I wanted but I still need to create a unique number in another column for each row. A easy and most elegant way around this is to do this: checksum(newid()). Doing a checksum on a newid() is guaranteed to be unique since newId() is itself unique.

Update: I happen to read that checksum() function is not guaranteed to give unique values. This is the case with SQL Server 2000 and I believe the same case with SQL Server 2005. For more information on checksum() collisions, take a look at this URL: http://blogs.clarience.com/davide/?p=11.

But, in my case when I implemented this solution (one time thing), I didn’t encounter duplicate entries in the column where I was inserting integer values. If I would have encountered duplicate entires, the underlying trigger or the column’s unique constraint setting would have thrown a error.

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

%d bloggers like this: