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

# Materialized View

> CREATE MATERIALIZED VIEW and SHOW MATERIALIZED VIEWS; refreshing is a REFRESH statement.

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

A materialized view stores a query's result as rows, kept current by a refresh.
It is the counterpart to a [view](https://docs.lancedb.com/sql/ddl/view.md), which stores only the query.

> [!NOTE]
>
> Materialized views are off by default in LanceDB Enterprise and have to be enabled
> there. Which statements and query shapes a deployment accepts, whether it needs
> the source table to have stable row ids, and how it refreshes a view depend on
> its LanceDB Enterprise version and configuration.

## CREATE MATERIALIZED VIEW

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
CREATE MATERIALIZED VIEW <name> AS SELECT ...
```

Once created, a materialized view can be queried like a table. Use the
statements below to list, refresh and drop it. For SDK methods, see
[Materialized views](https://docs.lancedb.com/management/materialized-view.md#create).

## SHOW MATERIALIZED VIEWS

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SHOW MATERIALIZED VIEWS
```

> [!NOTE]
>
> This asks the server for its views, as the SDK's `list_materialized_views` does
> on a `db://` connection.

## Refreshing one

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
REFRESH MATERIALIZED VIEW chunks
```

See [REFRESH MATERIALIZED VIEW](https://docs.lancedb.com/sql/dml/refresh-materialized-view.md) for the full syntax,
and compatibility notes for older server versions.

## Dropping one

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
DROP MATERIALIZED VIEW <name>
```

A dedicated statement; the SDKs emit it rather than
[`DROP TABLE`](https://docs.lancedb.com/sql/ddl/table#drop), even though a materialized view **is**
a table underneath. For the same reason, dropping one removes stored rows,
unlike dropping a [view](https://docs.lancedb.com/sql/ddl/view.md#drop).

> [!NOTE]
>
> **Enterprise job type for a refresh:** `sql`. Dropping, by contrast, runs the
> same underlying job as dropping any table, `drop_table` or `drop_object`.

## What to read next

> [card] [Materialized View](https://docs.lancedb.com/management/materialized-view.md)
>
> Creating one from the SDK.

> [card] [Refresh a materialized view](https://docs.lancedb.com/feature-engineering/refresh-materialized-view.md)
>
> How a refresh works, in LanceDB Enterprise.


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