I have a number of tables which have a primary key column set as as IDENTITY ALWAYS to generate the key. This is causing problems as I need to be able to copy the tables to a test database with preservation of the values and the identity column attribute.
I am looking at alternatives I can effect without starting the tables from scratch again. I would like to change the pk column to IDENTITY BY DEFAULT, but I havent found a way to do this,. Failing that I looked into removing the identity attribute using ALTER TABLE tbl DROP col IDENTITY and then maybe use ROW_NUMBER(), but with it originally being an identity column, the pk is not in sequence.
My question is, how will row_number() deal with the identity generated values and the gaps?