The main SQL data structure is the table. Tables are used for both storage and for
data manipulation. We’ve seen how to define tables with the CREATE TABLE command, but let’s look at some
of the details.
A table consists of a heading and a body. The heading defines the name and type (in SQLite, the affinity) of each column. Column names must be unique within the table. The heading also defines the order of the columns, which is fixed as part of the table definition.
The table body contains all of the rows. Each row consists of one data element for each column. All of the rows in a table must have the same number of data elements, one for each column. Each element can hold exactly one data value (or a NULL).
SQL tables are allowed to hold duplicate rows. Tables can contain multiple rows where every user-defined column has an equivalent corresponding value. Duplicate rows are normally undesirable in practice, but they are allowed.
The rows in an SQL table are unordered. When a table is displayed or written down, it will have some inherent ordering, but conceptually tables have no ordering. The order of insertion has no meaning to the database.
A very common mistake is to assume a given query will always return rows in the same order. Unless you’ve specifically asked a query to sort the returned rows in a specific order, there is no guarantee the rows will continue to come back in the same order. Don’t let your application code become dependent on the natural ordering of an unordered query. A different version of SQLite may optimize the query differently, resulting in a different row ordering. Even something as simple as adding or dropping an index can alter the row ordering of an unsorted result.
To verify your code is making no assumptions about row
order, you can turn on PRAGMA
reverse_unordered_selects. This will cause SQLite to reverse the natural row ordering of
any SELECT statement that does not have
an explicit order (an ORDER BY clause).
See reverse_unordered_selects in Appendix F for more details.