Informix Guide to SQL: Tutorial Using Functions in Select Statements

The database server assigns a unique rowid to rows in nonfragmented tables. However, rows in fragmented tables do not automatically contain the rowid column.

Informix recommends 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.

You can use a rowid to locate the internal record number that is associated with a row in a table. 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. Your rowid might vary from the following examples.

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

Query 4-52 uses the rowid and the wildcard asterisk symbol (*) in the SELECT clause to retrieve every row in the manufact table and their corresponding rowids.

SELECT  rowid,  *  FROM  manufact

 






 Query Result 4-52 







 

You can also use the rowid when you select a specific column, as Query 4-53 shows.

SELECT  rowid,  manu_code  FROM  manufact


 






 Query Result 4-53 







 

You can use the rowid in the WHERE clause to retrieve rows based on their internal record number. This method is handy when no other unique column exists in a table. Query 4-54 uses a rowid from Query 4-53.

SELECT  *  FROM  manufact WHERE  rowid  =  263

Query 4-54 returns the row that Query Result 4-54 shows.

Query Result 4-54

To obtain additional information about a table, you can combine the rowid with the USER function.

Query 4-55 assigns the label username to the USER expression column and returns this information about the cust_calls table.

SELECT  USER  username,  rowid  FROM  cust_calls


 






 Query Result 4-55 







 

You can also use the USER function in a WHERE clause when you select the rowid.

Query 4-56 returns the rowid for only those rows that are inserted or updated by the user who performs the query.

SELECT  rowid  FROM  cust_calls  WHERE  user_id  =  USER      

For example, if the user richc uses Query 4-56, the output is as Query Result 4-56 shows.

Query Result 4-56

You can add the DBSERVERNAME function (or its synonym, SITENAME) to a query to find out where the current database resides.

Query 4-57 finds the database server name and the user name as well as the rowid and the tabid, which is the serial-interval table identifier for system catalog tables.

SELECT  DBSERVERNAME  server,  tabid,  rowid,  USER  username  

	FROM  systables

	WHERE  tabid  >=  105 OR  rowid  <=  260

	ORDER  BY  rowid

Query 4-57 assigns display labels to the DBSERVERNAME and USER expressions and returns the 10 rows from the systables system catalog table, as Query Result 4-57 shows.

Query Result 4-57

Never store a rowid in a permanent table or attempt to use it as a foreign key because the rowid can change. For example, if a table is dropped and then reloaded from external data, all the rowids are different.