Find Out if a Table is WITHOUT ROWID in SQLite

One of SQLite’s unique features is the WITHOUT ROWID table, which can be used to optimize performance and storage in specific scenarios.

While it’s easy enough to create a WITHOUT ROWID table (just add WITHOUT ROWID to the definition), how to identify a WITHOUT ROWID table might not be so obvious.

In this article, we’ll start by briefly revising what WITHOUT ROWID tables are and how they differ from ordinary tables. Then we’ll look at how to identify these tables by using SQLite’s PRAGMA commands.

What is a `WITHOUT ROWID` Table?

By default, SQLite assigns a unique ROWID to every row in a table, even if the table has a separate PRIMARY KEY. This allows rows to be indexed and accessed by their ROWID.

We can leverage the ROWID for our primary key by defining a column as INTEGER PRIMARY KEY. When we do that, the column becomes an alias for ROWID. Therefore, we don’t need to insert separate values into the primary key column. We can simply use the existing ROWID that’s automatically generated by SQLite.

But if we define the table as WITHOUT ROWID, we no longer get the implicit ROWID. Instead, the table’s PRIMARY KEY becomes the sole mechanism for indexing and organizing rows. This can result in:

Better performance for tables with natural primary keys.
Reduced storage overhead because there’s no need for a separate ROWID.

However, the decision to define a table as WITHOUT ROWID shouldn’t be taken lightly. In many cases, you might be better off leveraging the ROWID. This should be a case-by-case decision, taking into account the data that each table will store, and how it will be used.

The Role of PRAGMAs in SQLite

SQLite offers PRAGMA commands as a way to inspect and modify database settings. Two useful PRAGMAs for understanding table structures are:

PRAGMA table_info: Displays the columns in a table, including their names, types, and constraints.
PRAGMA table_xinfo: Similar to table_info, but it also includes hidden columns (used in virtual tables).

However, these PRAGMAs alone don’t reveal whether a table is a WITHOUT ROWID table.

Identifying a `WITHOUT ROWID` Table Using `PRAGMA index_info`

The key to identifying a WITHOUT ROWID table lies in the PRAGMA index_info command. Normally, this PRAGMA is used to examine the details of an index on a table, such as the columns included in the index.

Here’s the important distinction:

For ordinary tables, PRAGMA index_info returns no rows if no explicit index exists.
For WITHOUT ROWID tables, PRAGMA index_info always returns information about the table’s PRIMARY KEY, even though no separate index was explicitly created.

This behavior provides a reliable way to determine if a table is WITHOUT ROWID. If PRAGMA index_info returns rows for the table’s primary key, you can conclude it’s a WITHOUT ROWID table.

Example: Comparing Ordinary and `WITHOUT ROWID` Tables

Let’s walk through an example to see this in action.

1. Create Two Tables

Let’s create an ordinary table and a WITHOUT ROWID table with similar structures:

-- Ordinary table
CREATE TABLE ordinary_table (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- WITHOUT ROWID table
CREATE TABLE without_rowid_table (
    id INTEGER PRIMARY KEY,
    name TEXT
) WITHOUT ROWID;

2. Use PRAGMA Commands

Inspect Table Columns

Let’s first use the PRAGMA table_info and PRAGMA table_xinfo commands to inspect the tables’ columns:

PRAGMA table_info('ordinary_table');
PRAGMA table_xinfo('ordinary_table');
PRAGMA table_info('without_rowid_table');
PRAGMA table_xinfo('without_rowid_table');

Output:

cid  name  type     notnull  dflt_value  pk
---  ----  -------  -------  ----------  --
0    id    INTEGER  0                    1 
1    name  TEXT     0                    0 

cid  name  type     notnull  dflt_value  pk  hidden
---  ----  -------  -------  ----------  --  ------
0    id    INTEGER  0                    1   0     
1    name  TEXT     0                    0   0     

cid  name  type     notnull  dflt_value  pk
---  ----  -------  -------  ----------  --
0    id    INTEGER  1                    1 
1    name  TEXT     0                    0 

cid  name  type     notnull  dflt_value  pk  hidden
---  ----  -------  -------  ----------  --  ------
0    id    INTEGER  1                    1   0     
1    name  TEXT     0                    0   0

Both tables return the same result.

Inspect Index Information

Running PRAGMA index_info reveals the difference:

PRAGMA index_info('ordinary_table');

This command returns no rows for the ordinary table.

Let’s run it on the WITHOUT ROWID table:

PRAGMA index_info('without_rowid_table');

For the WITHOUT ROWID table, this command returns:

seqno  cid  name
-----  ---  ----
0      0    id

This output indicates that the PRIMARY KEY acts as the index for the WITHOUT ROWID table.

Why Does This Matter?

Knowing whether a table is WITHOUT ROWID can be useful when optimizing for performance or troubleshooting. The distinction affects how rows are stored and indexed, which can impact query execution time and storage efficiency.

The PRAGMA index_info command provides a simple, unambiguous way to determine the table type.

Conclusion

SQLite’s WITHOUT ROWID tables offer performance and storage benefits for certain use cases. Understanding how to identify these tables using PRAGMA commands—especially PRAGMA index_info—empowers you to make informed decisions about your database design and maintenance.

What is a WITHOUT ROWID Table?