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:

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'

alter table tbl-name drop column PROGRESS_RECID_IDENT_

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

alter table tbl-name drop column PROGRESS_RECID_BKP
go

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.