Select Statement

You use the SQL Select statement to specify the columns and records to be read. Btrieve Select statements support all the Select statement clauses described in Chapter 11 “SQL Statements for Flat-File Drivers” in the DataDirect Connect Series for ODBC Reference. This section describes the information that is specific to Btrieve.

Rowid Pseudo-Column

Each Btrieve record contains a special column named Rowid. This field contains a unique number that indicates the record’s sequence in the database. You can use Rowid in Where and Select clauses.

Rowid is particularly useful when you are updating records. You can retrieve the Rowid of the records in the database along with the other field values. For example:

SELECT last_name, first_name, salary, rowid FROM emp

Then, you can use the Rowid of the record that you want to update to ensure that you are updating the correct record and no other. For example:

UPDATE emp set salary = 40000 FROM emp WHERE rowid=21

The fastest way of updating a single row is to use a Where clause with the Rowid. You cannot update the Rowid column.

Select statements that use the Rowid pseudo-column in the Where clause achieve maximum performance only for exact equality matches. If you use range scans instead of exact equality matches, a full table scan is performed. For example:

SELECT * FROM emp WHERE rowid=21    //fast search

SELECT * FROM emp WHERE rowid <=25  //full table scan