This is an interesting requirement I've been seeing at the public newsgroups. Most of the database designers do keep this in mind. But there this has become a common question that I thought I write some of the techniques I can think of.

Problem Definition

The table has an unique identifier as an identity column and we need to generate a running number that can be used as an identity column instead of the uniqueidentifier column. the table will be generated as below:

So now we have our table with values.

Method 1

Next is we need to alter the table and add a column to generate the running number. And then we loop through the rows and generate the number. The simple code will look like:

But here I've used an undocumented approach to generate the running number in SQL. This is an interesting implementation. Here I need to add the Identity attibute to the column after generating the running number. But the best one I found was the one given below.

Method 2

This is the simplest of all. We need to have a identity column, hence while defining the column definition in the alter table we need to add the clause for Identity column. The code will look like below.

This requirement as such is quite debatable in nature. I personally from my experience feel that an identity column value is far more better as an identifier rather than an UniqueIdentifier. For a matter of fact, I would ask people to avoid using the 16 bit unique GUID's in their DB design. Dont use it unless it mandates for one. Like a mapping of a UserGUID mapping to a AD (Active Directory) structure object. Apart from cases like these the need to use GUID's is not justified. Generally these type of columns will be used as Clustered Indexes. And using GUID's for Clustered indexes pose their own problem. GUID's would induce page splits if we have not definied FILL_FACTOR. And re-organizing the index pages is something costlier, time and resource consuming. We would not like to do this often. And this is not suitable for a OLTP applications atleast. But the counter arguement would be that running numbers usage will induce Hot spots on OLTP applications on high running transactions. But I personally feel that these are less dangerous when compared to the page splits. These are my personal views from the work I've done so far. But again these can be put under microscope and again examined depending upon the type of applications we write. So do consider these points.

Conclusion

As described there can be multiple ways in which this can be achieved. But I've illustrated some simple techniques you can ping upon. Please do feel free to get back to us with your views on the same.