Row IDs in tables

The rowid is a common feature in most databases. It identifies a specific record in the database. The rowid is guaranteed to be unique within a table and unique across the entire system, but not necessarily sequential within a table.

When you run the nzload command, the host creates records and assigns rowids. The SPUs can also create records and assign rowids, which happens when you use the command CREATE TABLE <tablename> AS SELECT.

The system gives the host and each of the SPUs a block of sequential rowids that they can assign. When they use up a block, the system gives them another block, which explains why the rowids within a table are not always sequential.

The system stores the rowid with each database record. It is an 8-byte integer value.

You can use the rowid keyword in a query to select, update, or delete records. For example:

SELECT rowid, lname FROM employee_table; 
UPDATE employee_table SET lname = ‘John Smith’ WHERE rowid = 234567;

Querying by some other field, such as name, might be difficult if you have ten John Smiths in the database.

In a new installation, the initial rowid value is 100,000. The next available rowid value is stored in the /nz/data/RowCounter file.