Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

> ## Modifying tables to create PROGRESS_RECID in support of the ROWID function

> If you want to use the `ROWID` function with a data source, you must select an index to associate with this function. Adding a new integer column named `PROGRESS_RECID` and defining it as a single-component index is the preferred method. This method can only be applied to a table that does not currently have an identity column defined. If your table already has an identity column, you can support `ROWID` by selecting an existing index. See the "Defining the ROWID" section for instructions on selecting an existing index. The following procedure illustrates how to create the `PROGRESS_RECID` column to support `ROWID`.

> To use the `ROWID` function with an MS SQL Server database, you must make changes to your data source table.

> | To modify a table to support the ROWID function:

> 1. Perform these tasks:

> 1. Add a column of the `integer` data type named `PROGRESS_RECID`. The new column must be able to contain `null`, as shown:

>     alter table table
>         add PROGRESS_RECID integer null

> 2. Add a column with identity characteristics named `PROGRESS_RECID_IDENT_`. The new column must have the `integer` data type, as shown:

>     alter table table
>         add PROGRESS_RECID_IDENT_
>     integer identity

> 2. Create a trigger to maintain the `PROGRESS_RECID` column:

>     create trigger _TI_table ON table for insert as
>         if  ( select PROGRESS_RECID from inserted) is NULL
>           begin
>               update t set PROGRESS_RECID = i.IDENTITYCOL
>                 from table t  JOIN inserted i ON
>                  t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
>                  select convert (int, @@identity)
>           end

> 3. Create a non-unique index for the `PROGRESS_RECID` and a unique index for the `PROGRESS_RECID_IDENT`, respectively, according to the following syntax:

>     create index <table>#_#progress_recid on <table> (PROGRESS_RECID)

>     create unique index <table>#_#progress_recid_ident_on <table>
>       (PROGRESS_RECID_IDENT_)

> 4. Change the nonunique indexes so that they include a `PROGRESS_RECID` column as the last component, as shown:

>     create index <table>#_#<index> on table (column, PROGRESS_RECID)

> 5. If you have already created your schema holder, delete and recreate it.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095