Migrating RECID-trigger mechanism to RECID computed column mechanism

Use the following procedure to convert a table that supports the ROWID function from one that is trigger-enabled to the more reliable computed column-enabled. Computed column enablement is available only through MS SQL Server 2005 and later.

Note: Prior to performing the following steps, ensure you have backed up the tables with which you will be working.

To adopt a table to support the ROWID function using computed column from trigger mechanism:

  1. Drop the trigger on INSERT for PROGRESS_RECID column as: 
    DROP TRIGGER _TI_tbl-name 
GO
  2. Store the current identity value on the table as follows: 
    select ident_current('schemaname.tbl-name') as old_identity_value
go
  3. Drop the indexes on PROGRESS_RECID, PROGRESS_REID_IDENT_ columns as: 
    drop index table#_#progress_recid on table
drop index table#_#progress_recid_ident_on table
  4. Drop the other indexes which include PROGRESS_RECID column as: 
    drop index table#_#index on table
  5. Rename the PROGRESS_RECID column to PROGRESS_RECID_bkp as: 
    EXEC sp_rename 'tbl-name.PROGRESS_RECID', 'PROGRESS_RECID_BKP' ,'COLUMN'
  6. Drop the PROGRESS_RECID_IDENT_ column as: 
    alter table tbl-name drop column PROGRESS_RECID_IDENT_
  7. Add new columns for computed column mechanism as: 
    ALTER TABLE tbl-name
ADD PROGRESS_RECID AS
          CASE WHEN PROGRESS_RECID_ALT_ is null
          THEN PROGRESS_RECID_IDENT_
          ELSE PROGRESS_RECID_ALT_
          END PERSISTED not null,
PROGRESS_RECID_IDENT_ bigint identity,
PROGRESS_RECID_ALT_ bigint null default NULL, 
CONSTRAINT tbl-name#_#progress_recid UNIQUE(PROGRESS_RECID)
GO
  8. Update the PROGRESS_RECID_ALT_ column with PROGRESS_RECID_BKP as: 
    update tbl-name set PROGRESS_RECID_ALT_ = PROGRESS_RECID_BKP
go
  9. Reseed the altered table with previously stored identity value (in step-2)as: 
    DBCC CHECKIDENT ('tbl-name', RESEED, <old-identity-value>)
go
  10. Drop the PROGRESS_RECID_BKP column as: 
    alter table tbl-name drop column PROGRESS_RECID_BKP 
go
  11. Re-create the dropped indexes during Step 4 as: 
    create index table#_#index on table (column, PROGRESS_RECID)
go
  12. If you have already created your schema holder, delete and recreate it.
Note: It is recommended that you back-up database tables before following the steps to adopt a new computed column mechanism.