Change in SQLServer the key column of a table to a new one of type integer that is identity using OVER

It may be that at some point it may be necessary to change the type of key column for our table / s for a bad design or a subsequent change x that forces us to do so. If we do and the new has to be a column of the integer type, maybe identity, we can do it with some criterion so that it is sorted (pk = clustered index = order by on disk by that field).

In this example to make it more complete, we do it in two Master-Detail tables where the key fields are type nchar (HEADERS.ID_CABECERA and LINEAS.ID_LINEA). Steps to follow...

  • We start by choosing the column type and add it to the main table, HEADERS. We continue with an update that starts from a join with a select on the same table including the ROW_NUMBER () function for the line counter that will serve as an autoincremental value (identity?).
USE DBTEST;

ALTER TABLE CABECERAS ADD PK_CABECERA int NULL;

UPDATE CABECERAS SET PK_CABECERA = A.NUMERO_FILA
FROM CABECERAS 
     INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY FECHA ASC) AS NUMERO_FILA
         FROM CABECERAS) A ON CABECERAS.ID_CABECERA = A.ID_CABECERA;


  • Then we continue with the detail, LINEAS, where we do the same as before with your own PK. To maintain the same order, we join with the HEADSET table and take advantage of it to assign the value of the new foreign key as well.
USE DBTEST;

ALTER TABLE LINEAS ADD PK_LINEA int NULL;
ALTER TABLE LINEAS ADD FK_CABECERA int NULL;

UPDATE LINEAS SET PK_LINEA = A.NUMERO_FILA, 
 FK_CABECERA = PK_CABECERA
FROM LINEAS 
      INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY FECHA ASC) AS NUMERO_FILA, ID_LINEA, PK_CABECERA
        FROM LINEAS INNER JOIN CABECERAS ON CABECERAS.ID_CABECERA=LINEAS.ID_CABECERA) A ON LINEAS.ID_LINEA = A.ID_LINEA;

Now that we have the new candidates for key field, we will mark them as NOT NULL, as a key field and we will opt if necessary to put them as identity. We will also modify the FOREIGN KEY or add a new one. A very easy way to do this is to do it visually, using Management Studio, in the Database Diagrams part (in the Object Explorer tree).

Relational scheme master detail

By the way, if you finally need the new keys to be identity we must first recover the maximum value of the field in the two tables to assign the initial value of the identity. If we want to be more purists, we must know that since SqlServer 2012 there are sequences and we could do the same using them.

It is interesting to know about a possible database type migration. Namely, Oracle also allows the use of sequences but does not exist equivalent to identity fields. MySql instead has identity and is AUTO_INCREMENT.