Adding an identity to an existing column

Strategy to add identity

Points to ponder before adding identity

If you're considering adding an identity column, a few important considerations are:

• Existing data: A new identity column becomes a clean slate, meaning it won't retain previous data.
• Continuity: Use DBCC CHECKIDENT after migration to maintain numeric continuity.
• Safety nets: Use IF EXISTS checks to prevent accidental deletion of non-existent tables.
• Updating relationships: Make sure to update any foreign keys, indexes, or SCHEMABINDING dependent on the old column.

Inserting identity into substantial tables

When dealing with a large table, ALTER TABLE...SWITCH can be a game-changer!

• Decrease downtime: The table remains available during large parts of the process.
• Beware: It's a complex method, not unlike defusing a bomb while riding a unicycle.

Here's your switch script:

BEGIN TRANSACTION;

-- Some consider this a "table swap". Doesn't it sound fun to switch things around? 😮
ALTER TABLE OldTable SWITCH TO NewTable;
ALTER TABLE NewTable ADD NewID INT IDENTITY(1,1);

-- It's more fun in identity mode! 🌈
SET IDENTITY_INSERT NewTable ON;
INSERT INTO NewTable (NewID, ...) SELECT OldID, ... FROM OldTable;
SET IDENTITY_INSERT NewTable OFF;

-- Making sure OldTable doesn't feel abandoned...
DROP TABLE OldTable;
EXEC sp_rename 'NewTable', 'OldTable';

COMMIT TRANSACTION;


Choosing sequence over identity

In the SQL Server world, the SEQUENCE object can sometimes be a better option to auto-increase primary keys:

• Adaptability: Multiple tables can utilize a sequence.
• Conflict resolution: If you're going to auto increment, then do it correctly with SEQUENCE to avoid conflictions.

Please refer to the following script to initiate sequence:

CREATE SEQUENCE Seq_As_Identity
    START WITH 1
    INCREMENT BY 1;

ALTER TABLE MyTable
    ADD NewIDCol INT DEFAULT NEXT VALUE FOR Seq_As_Identity
    CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED;

-- Because who needs that old ID column anyway, right? 😂    
ALTER TABLE MyTable DROP COLUMN OldIDCol;


Maneuvering in active tables

Dealing with active tables comes with its own set of rules:

• Preventing conflicts: Setting the identity seed above the highest existing ID helps you dodge potential mix-ups.
• Sequential flow: You'd want to ensure new inserts do not pick a fight with the transformation process.

Crafting the identity addition script

The SQL Management Studio lets you auto-generate an ALTER script. Talk about ordering a custom pizza! 🍕

• Kick-start from the table design view.
• Implement your changes, i.e., add an identity column.
• Choose Generate Change Script instead of hitting Save.

Cleaning up post-migration

Following a successful reformation:

• Release space: Wave goodbye to the old tables.
• Consistency in naming: sp_rename is your magic spell!