Skip to main content
Enterprise only

CREATE TABLE

Column types are Arrow types under SQL names. See Data types. A table inside a namespace is written with its schema before the name: `prod$events`.clicks.

DESCRIBE

The table’s columns and types, without a plan. The SQL counterpart of table.schema.

ALTER TABLE

What ALTER TABLE ... ADD COLUMN does depends on what follows the column name.

A plain column

SQL ADD COLUMN requires an AS binding, so a plain column such as ALTER TABLE t ADD COLUMN c INT is rejected. Add an empty, nullable column with the SDK instead. See Add nullable columns.

A computed column

AS <expression> declares a computed column. The column is committed empty and the expression is stored, not evaluated: declaring one on a billion-row table costs what declaring one on an empty table costs. REFRESH COLUMN fills it.

A function column

When the expression calls a registered function, the column is filled by running that function on workers. version pins which immutable function version fills it, so a later registration does not silently change what the column means. Several modifiers narrow what gets computed: A function returning several values fills several columns at once:
Declaration WITH accepts auto_refresh_enabled and auto_refresh_interval_seconds. Function bindings also accept version and branch. Worker resource options such as num_workers belong on REFRESH COLUMN ... WITH (...).

Table properties

Records which column identifies a row. Unenforced is not a hedge in the naming: uniqueness is not checked on write, and declaring it does not make duplicates impossible. It tells the planner what to assume. The column must be declared NOT NULL.
The Realtime Engine puts a write-ahead log in front of the table, making small frequent writes cheap by batching them out of the commit path. sharding and buckets spread the log so concurrent writers do not contend on one place; bucket sharding names its column, which must be the table’s unenforced primary key, and inserts into the table then need a scalar index on that key. maintained_indexes chooses which indexes are kept current against WAL rows; omitted, every supported index is kept current, including ones created later. A table with a computed column cannot take a write-ahead log. See RTE Compaction for how the log gets folded back into the table.
TABLESAMPLE is refused on a WAL-backed table, because rows a sample would draw from are not all in the fragments yet.

Errors and limits

Dropping, renaming, or retyping a column is an SDK operation (drop_columns and alter_columns), not a SQL statement.

CLONE

A shallow clone: the new table shares the source’s data files rather than copying them, so it costs one metadata commit regardless of the source’s size. AT pins the point in the source’s history to clone. A bare integer is a version and a bare quoted string is a timestamp; VERSION, TAG and TIMESTAMP say so explicitly, and a tag always needs its keyword: a bare quoted string cannot be both a tag name and a timestamp.
The clone depends on the source’s files continuing to exist. If the source is compacted past the retention window, the version the clone points at can be pruned. The SDK’s clone_table also offers a deep clone for when the copy has to outlive the source’s history.

DROP TABLE

Removes the table and every version of it. There is no undrop.
Enterprise job type: drop_table or drop_object, depending on how the deployment stores its catalog. See Object teardown.

What to read next

Table

The SDK equivalent, and what a table carries.

Schema evolution

Renaming, retyping and dropping columns from the SDK.

SHOW TABLES

Returns two non-null string columns: namespace_path, a compact JSON array of parent namespace components ([] for root), and name, the unqualified object name. Omitting IN lists all authorized namespaces in the current database; IN team$ml selects that exact path without a database qualifier; IN public selects the current database’s root. Listings require the relevant database and namespace USAGE privileges. Page through results using LIMIT n [FROM '<page-token>']. Read the opaque lancedb.next_page_token from the Arrow result schema metadata and pass it back unchanged with the same object type and scope. Stop when no token is returned; a short or empty page can still carry a continuation. See catalog listing pagination.