> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Table

> CREATE, DESCRIBE, ALTER, CLONE and DROP TABLE.

**Applies to:** Embedded ❌, Enterprise ✅

## CREATE TABLE

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
CREATE TABLE documents (id BIGINT, title VARCHAR, created_at TIMESTAMP)
CREATE TABLE recent AS
SELECT * FROM documents
WHERE created_at > now() - INTERVAL '7 days'
```

Column types are Arrow types under SQL names. See [Data types](https://docs.lancedb.com/sql/data-types.md).

A table inside a [namespace](https://docs.lancedb.com/sql/ddl/namespace.md) is written with its
[schema](https://docs.lancedb.com/basics/catalog.md#schema) before the name: `` `prod$events`.clicks ``.

## DESCRIBE

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
DESCRIBE documents
```

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](https://docs.lancedb.com/management/table.md#schema-add-nullable-columns).

### A computed column

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
ALTER TABLE t ADD COLUMN doubled AS value * 2
```

`AS <expression>` declares a [computed column](https://docs.lancedb.com/feature-engineering/add-computed-column.md).
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`](https://docs.lancedb.com/sql/dml/refresh-column) fills it.

### A function column

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
ALTER TABLE t ADD COLUMN embedded AS embed(name) WITH (version = 'fv_1')
```

When the expression calls a [registered function](https://docs.lancedb.com/sql/ddl/function.md), 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:

| | |
| - | - |
| `FILTER (WHERE pred)` | only rows matching `pred` are computed |
| `IGNORE NULLS` | rows with null inputs are skipped |
| `OVER (PARTITION BY col)` | the function sees rows grouped by `col` |

A function returning several values fills several columns at once:

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
ALTER TABLE t ADD COLUMN (label, score) AS classify(text) WITH (version = 'fv_1')
```

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
ALTER TABLE t ADD COLUMN c AS f(x) FILTER (WHERE keep) WITH (version = 'fv_1')
REFRESH COLUMN c ON t WITH (num_workers = 2)
```

> [!NOTE]
>
> 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

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
ALTER TABLE t SET UNENFORCED PRIMARY KEY (id)
```

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`.

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
ALTER TABLE t SET RTE
ALTER TABLE t SET RTE WITH (sharding = 'bucket', column = 'id', buckets = 4)
ALTER TABLE t SET RTE WITH (maintained_indexes = '')
ALTER TABLE t UNSET RTE
```

The [real time engine](https://docs.lancedb.com/tables/realtime-engine.md) 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](https://docs.lancedb.com/enterprise/realtime-engine-compaction.md) for how
the log gets folded back into the table.

> [!WARNING]
>
> [`TABLESAMPLE`](https://docs.lancedb.com/sql/dql/sampling) is refused on a WAL-backed table, because
> rows a sample would draw from are not all in the fragments yet.

### Errors and limits

| | |
| - | - |
| `ALTER TABLE ... DROP COLUMN` | not supported |

Dropping, renaming, or retyping a column is an SDK operation
([`drop_columns`](https://docs.lancedb.com/management/table#schema) and
[`alter_columns`](https://docs.lancedb.com/management/table#schema)), not a SQL statement.

## CLONE

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
CREATE TABLE docs_snapshot CLONE documents
CREATE TABLE docs_at_v42 CLONE documents AT 42
CREATE TABLE docs_at_release CLONE documents AT TAG 'release-1'
CREATE TABLE docs_yesterday CLONE documents AT TIMESTAMP '2026-09-01T00:00:00Z'
```

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.

> [!WARNING]
>
> The clone depends on the source's files continuing to exist. If the source is
> [compacted](https://docs.lancedb.com/maintenance/cleanup.md) past the retention window, the version the
> clone points at can be pruned. The SDK's
> [`clone_table`](https://docs.lancedb.com/management/table#rename-clone) also offers a deep clone for
> when the copy has to outlive the source's history.

## DROP TABLE

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
DROP TABLE documents
```

> [!WARNING]
>
> Removes the table and every version of it. There is no undrop.

> [!NOTE]
>
> **Enterprise job type:** `drop_table` or `drop_object`, depending on how the
> deployment stores its catalog. See
> [Object teardown](https://docs.lancedb.com/basics/jobs.md#teardown).

## What to read next

> [card] [Table](https://docs.lancedb.com/management/table.md)
>
> The SDK equivalent, and what a table carries.

> [card] [Schema evolution](https://docs.lancedb.com/management/table.md#schema)
>
> Renaming, retyping and dropping columns from the SDK.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.