I was recently talking to a friend in our local pub about a problem he had with tables not using IDENTITY columns. His application was incrementing the max value of the ID column in each table by 1. This isn’t particularly efficient and it’s potentially open to race conditions.

As we moved onto our 2nd ale he told me his plan was to start using IDENTITY columns. He was frustrated at not being able to add the identity property to his ID columns with an ALTER TABLE statement. For this reason his plan was to create identical tables (with INDENTITY set to on, on the ID columns) , copy the data over using IDENTITY_INSERT, DROP the old tables and rename the new. He was planning to do this as a release so all of these changes would be in one deployment script.

During the 3rd and 4th pint I explained the stress this would put on his customers transaction logs, storage, memory, etc. There must be a better way.

Then suddenly, half through a pork scratching, an idea struck me. Would the SWITCH command let you get away with the difference in column properties? SWITCHing the data to the new tables would be much better than a full copy.

The SWITCH statement can instantly ‘move’ data from one table to another table. It does this by updating some meta data, to say that the new table is now the owner of the data instead of the old table. This is very useful as there is no physical data movement to cause the stresses mentioned earlier. There are a lot of rules enforced by SQL Server before it will allow this to work. Essentially each table must have the same columns with the same data types and NULL settings, they need to be in the same file group and  the new table must be empty. See here for a more detailed look at these rules.

On the way home I gave the SWITCH command a 20% chance of working because I thought there was bound to be a rule that said column properties needed to be the same in the old and the new table.

When you’re not sure of something in SQL Server it’s time to write a test script. Below is the script I put together with a clear head the next day.

CREATE TABLE TestNoIdentity
(
  IDINT NOT NULL
  ,Name VARCHAR(50)
);
GO
-- Insert 100 rows into TestNoIdentity
;WITH cte_Ten
AS
(
SELECT *
FROM(
      VALUES(1, 'James')
          , (1, 'James')
          , (1, 'James')
          , (1, 'James')
          , (1, 'James')
          , (1, 'James')
          , (1, 'James')
          , (1, 'James')
          , (1, 'James')
          , (1, 'James')
    ) AS a(n, Name)
)
INSERT TestNoIdentity
SELECT ROW_NUMBER() OVER (ORDER BY a.n), a.Name
FROMcte_Ten a
CROSS JOIN cte_Ten b;
GO
CREATE TABLE TestHasIdentity
(
  IDINT IDENTITY(1,1)
  ,Name VARCHAR(50)
);
GO
ALTER TABLE TestNoIdentity SWITCH TO TestHasIdentity
GO
-- Reseed identity column 
DBCC CHECKIDENT ('TestHasIdentity', RESEED)
-- Test the identity works by adding a row
INSERT TestHasIdentity (Name)
SELECT 'Test'
SELECT * FROM TestHasIdentity ORDER BY ID DESC
DROP TABLE TestNoIdentity
DROP TABLE TestHasIdentity

I was very surprised to see the script complete without errors. It’s always worth testing these things.

The last step would be to DROP the old table and use sp_rename() to give the new table the old name.

Conclusion

It is not possible to alter a column with existing values to be an IDENTITY column, but there is a work around using the ALTER TABLE…SWITCH statement.

The post SWITCHing IDENTITY in the Pub appeared first on The Database Avenger.