Add Identity Column to Table Based on Order of Another Column

http://blog.sqlauthority.com/2013/05/30/sql-server-add-identity-column-to-table-based-on-order-of-another-column/

I already have existing table and the table already have fewer columns. The table does not have identity column and I would like to add an identity column to this table. The problem is that every time when I try to add an identity column to the table, it adds the value based on the default order of the table. I would like to add the identity value based on the order sequence of another column from the table. Do you have any alternative to it. To illustrate my problem here is the simple script based on my table schema. My table also do not have any index as of now.

USE tempdb
GO-- Create TableCREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))GO-- Insert DataINSERT INTO TestTable (Col1, Col2)VALUES (33, 'Pinal');INSERT INTO TestTable (Col1, Col2)VALUES (22, 'Nupur');INSERT INTO TestTable (Col1, Col2)VALUES (11, 'Shaivi');GO-- Select DataSELECT *FROM TestTable
GO-- Add Identity ColumnALTER TABLE TestTableADD ID INT IDENTITY(1, 1)GO-- Select DataSELECT *FROM TestTable
GO-- Clean upDROP TABLE TestTable
GO

Here is the result set of the query above. Currently the result is ordered by the column Col1 DESC but ideally I would like to get the result ordered by Col1 but in ASC order.

Is there any workaround to do the same?”

As I said I find this question very interesting and I was able to come up with the solution as well fairly quickly as the user had not created an index on the table. I quickly created clustered index in ASC order on Col1 and it ordered the table as expected and later added the identity column there. Let us see the script to get the desired result.

USE tempdb
GO-- Create TableCREATE TABLE TestTable (Col1 INT, Col2 VARCHAR(100))GO-- Insert DataINSERT INTO TestTable (Col1, Col2)VALUES (33, 'Pinal');INSERT INTO TestTable (Col1, Col2)VALUES (22, 'Nupur');INSERT INTO TestTable (Col1, Col2)VALUES (11, 'Shaivi');GO-- Select DataSELECT *FROM TestTable
GO-- Create Clustered Index on Column IDCREATE CLUSTERED INDEX IX_TestTable ON dbo.TestTable(Col1 ASC)GO-- Add Identity ColumnALTER TABLE TestTableADD ID INT IDENTITY(1, 1)GO-- Select DataSELECT *FROM TestTable
GO-- Clean upDROP TABLE TestTable
GO

Above script will produce following answer which user is expecting:

Now here is my question back to, this was fairly simple for me to do as there was no index created on the table. I was able to create clustered index on the column and get the desired result. However, what will be the alternative solution to this question if the clustered index is already created on the table and there was no option to modify the same. Another alternative solution would be to drop the table and do processing but that is never a good solution as well, it is not possible if there are foreign keys exists on the table