Rowid values in SELECT statements

The database server assigns a unique rowid to rows in nonfragmented tables. The rowid is, in effect, a hidden column in every table. The sequential values of rowid have no special significance and can vary depending on the location of the physical data in the chunk. You can use a rowid to locate the internal record number that is associated with a row in a table. Rows in fragmented tables do not automatically contain the rowid column.

It is recommended that you use primary keys as a method of access in your applications rather than rowids. Because primary keys are defined in the ANSI specification of SQL, using them to access data makes your applications more portable. In addition, the database server requires less time to access data in a fragmented table when it uses a primary key than it requires to access the same data when it uses rowid.

For more information about rowids, see the IBM® Informix® Database Design and Implementation Guide and your HCL Informix Administrator's Guide.

The following query uses the rowid and the wildcard asterisk symbol (*) in the Projection clause to retrieve each row in the manufact table and its corresponding rowid.

Figure 1. Query

SELECT rowid, * FROM manufact;

Figure 2. Query result

rowid manu_code manu_name       lead_time 

  257 SMT       Smith              3
  258 ANZ       Anza               5
  259 NRG       Norge              7
  260 HSK       Husky              5
  261 HRO       Hero               4
  262 SHM       Shimara           30
  263 KAR       Karsten           21
  264 NKL       Nikolus            8
  265 PRC       ProCycle           9

Never store a rowid in a permanent table or attempt to use it as a foreign key. If a table is dropped and then reloaded from external data, all the rowids will be different.