12 Easy steps to remove identity column in SQL server.
Sometimes we face a problem with primary key column in database with uncertain sequence generated due to deleting some data from table or SQL server sequence jump when SQL server restarts. To avoid this problem we plans to turn of auto sequence generated by identity column. It seems very easy in case of only one table. what happen if more than one table is present with in database with foreign keys dependency.
Step 1: Create a duplicate table for backup tbdup_tbCountry.
Step 2: Check data in both tables SWC.tbdup_tbCountry and SWC.tbCountry using count.
Step 3: Add a new column for primary key column dup.
Step 4: Copy data from primary key column to new column dup.
Step 5: Match data of both primary key column and dup column.
Step 6: Remove all constraints from table.
Step 7: DELETE primary key column.
Step 8: Add new column with same name as primary key column without any constraints.
Step 9: Copy data from dup column to new created column (on which we will apply constraints).
Step 10: Create all possible constraints with primary key.
Step 11: If every this is good drop dup column.
Step 12: Drop duplicate table.