Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate how to remap a non-sequential key (column) to sequential numbering.

/* SQL Keys quick explanation
   With reference to the following tables
   PRIMARY KEYs: ProductID, ProductPhotoID - unique row identifier
   FOREIGN KEY: ProductPhoto(ProductID) - provides valid JOIN link to PK table
   UNIQUE KEY: ProductName - prevents duplicates
*/
CREATE TABLE Product(
  ProductID int identity(1,1) PRIMARY KEY, -- Clustered Unique Index created
  ProductName nvarchar(64) not null UNIQUE, -- Non-Clustered Unique Index created
  ModifiedDate date default(getdate()));
 
CREATE TABLE ProductPhoto (
  ProductPhotoID int identity(1,1) PRIMARY KEY, -- Clustered Unique Index created
  ProductID int REFERENCES Product(ProductID),
  LargePhoto varbinary(max),
  ModifiedDate date default(getdate()));

-- SQL sequential numbering - SQL sequential update

-- SQL map non-sequential numbers to sequential numbers

-- SQL map id with gaps to sequential id - SQL select into create table

/***** NOTE *****

Remapping Primary Keys requires remapping Foreign Keys also

*/

USE tempdb;

SELECT *

INTO   SODetail

FROM   AdventureWorks2008.Sales.SalesOrderDetail

GO

-- (121317 row(s) affected)

SELECT   TOP 100 *

FROM     SODetail

ORDER BY SalesOrderID,

         SalesOrderDetailID

/* Partial results

SalesOrderID      SalesOrderDetailID      CarrierTrackingNumber

43659 1     4911-403C-98

43659 2     4911-403C-98

43659 3     4911-403C-98

43659 4     4911-403C-98

43659 5     4911-403C-98

43659 6     4911-403C-98

43659 7     4911-403C-98

43659 8     4911-403C-98

43659 9     4911-403C-98

43659 10    4911-403C-98

43659 11    4911-403C-98

43659 12    4911-403C-98

43660 13    6431-4D57-83

43660 14    6431-4D57-83

43661 15    4E0A-4F89-AE

43661 16    4E0A-4F89-AE

43661 17    4E0A-4F89-AE

43661 18    4E0A-4F89-AE

43661 19    4E0A-4F89-AE

43661 20    4E0A-4F89-AE

43661 21    4E0A-4F89-AE

43661 22    4E0A-4F89-AE

43661 23    4E0A-4F89-AE

43661 24    4E0A-4F89-AE

43661 25    4E0A-4F89-AE

43661 26    4E0A-4F89-AE

43661 27    4E0A-4F89-AE

43661 28    4E0A-4F89-AE

43661 29    4E0A-4F89-AE

43662 30    2E53-4802-85

43662 31    2E53-4802-85

43662 32    2E53-4802-85

43662 33    2E53-4802-85

43662 34    2E53-4802-85

43662 35    2E53-4802-85

43662 36    2E53-4802-85

43662 37    2E53-4802-85

43662 38    2E53-4802-85

43662 39    2E53-4802-85

43662 40    2E53-4802-85

43662 41    2E53-4802-85

43662 42    2E53-4802-85

43662 43    2E53-4802-85

43662 44    2E53-4802-85

43662 45    2E53-4802-85

43662 46    2E53-4802-85

43662 47    2E53-4802-85

43662 48    2E53-4802-85

43662 49    2E53-4802-85

43662 50    2E53-4802-85

43662 51    2E53-4802-85

43663 52    1E90-4FBF-B6

43664 53    2F44-4BA1-BB

*/

-- SQL Common Table Expression - CTE

-- T-SQL identity function

;

WITH cteDistinctSO

     AS (SELECT DISTINCT SalesOrderID

         FROM   SODetail)

SELECT   [SequentialID] = Identity(INT,1,1),

         SalesOrderID

INTO     #SequentialMapping

FROM     cteDistinctSO

ORDER BY SalesOrderID ASC

GO

--(31465 row(s) affected)

SELECT   TOP 10 *

FROM     #SequentialMapping

ORDER BY SalesOrderID

GO

/* 

SequentialID      SalesOrderID

1     43659

2     43660

3     43661

4     43662

5     43663

6     43664

7     43665

8     43666

9     43667

10    43668

*/

-- SQL Server sequential update

UPDATE s

SET    s.SalesOrderID = m.[SequentialID]

FROM   SODetail s

       INNER JOIN #SequentialMapping m

         ON s.SalesOrderID = m.SalesOrderID

GO

SELECT   TOP 100 *

FROM     SODetail

ORDER BY SalesOrderID,

         SalesOrderDetailID

/* Partial results

SalesOrderID      SalesOrderDetailID      CarrierTrackingNumber

1     1     4911-403C-98

1     2     4911-403C-98

1     3     4911-403C-98

1     4     4911-403C-98

1     5     4911-403C-98

1     6     4911-403C-98

1     7     4911-403C-98

1     8     4911-403C-98

1     9     4911-403C-98

1     10    4911-403C-98

1     11    4911-403C-98

1     12    4911-403C-98

2     13    6431-4D57-83

2     14    6431-4D57-83

3     15    4E0A-4F89-AE

3     16    4E0A-4F89-AE

3     17    4E0A-4F89-AE

3     18    4E0A-4F89-AE

3     19    4E0A-4F89-AE

3     20    4E0A-4F89-AE

3     21    4E0A-4F89-AE

3     22    4E0A-4F89-AE

3     23    4E0A-4F89-AE

3     24    4E0A-4F89-AE

3     25    4E0A-4F89-AE

3     26    4E0A-4F89-AE

3     27    4E0A-4F89-AE

3     28    4E0A-4F89-AE

3     29    4E0A-4F89-AE

4     30    2E53-4802-85

4     31    2E53-4802-85

4     32    2E53-4802-85

4     33    2E53-4802-85

4     34    2E53-4802-85

4     35    2E53-4802-85

4     36    2E53-4802-85

4     37    2E53-4802-85

4     38    2E53-4802-85

4     39    2E53-4802-85

4     40    2E53-4802-85

4     41    2E53-4802-85

4     42    2E53-4802-85

4     43    2E53-4802-85

4     44    2E53-4802-85

4     45    2E53-4802-85

4     46    2E53-4802-85

4     47    2E53-4802-85

4     48    2E53-4802-85

4     49    2E53-4802-85

4     50    2E53-4802-85

4     51    2E53-4802-85

5     52    1E90-4FBF-B6

6     53    2F44-4BA1-BB

*/

-- Cleanup

DROP TABLE tempdb.dbo.SODetail

DROP TABLE #SequentialMapping

GO