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

# Enterprise SQL function index

> Find built-in SQL functions and LanceDB Enterprise functions for vectors, blobs, and deduplication.

Enterprise only

LanceDB Enterprise provides scalar, aggregate, window, and table functions. Use
this index to choose a function for your workload. Availability depends on the
server version deployed to your cluster. For standard functions and their dialect
version, see the [SQL functions overview](https://docs.lancedb.com/enterprise/v1-14-0/sql/dql/functions.md) and
[scalar function reference](https://docs.lancedb.com/enterprise/v1-14-0/sql/dql/scalar-functions.md). This page adds Enterprise
discovery and workload-specific functions. For help choosing text or vector
matching and running a deduplication workflow, start with the
[deduplication guide](https://docs.lancedb.com/enterprise/v1-14-0/curation/dedupe.md).

## Discover built-in functions

On server versions that support `SHOW BUILTIN FUNCTIONS`, query the running
engine for its scalar, aggregate, window, and table function catalog:

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SHOW BUILTIN FUNCTIONS;
SHOW BUILTIN FUNCTIONS LIKE '%distance%';
SHOW BUILTIN FUNCTIONS LIKE 'minhash%';
```

The result has `name`, `kind`, `syntax`, and `description` columns. Names include
aliases and are sorted alphabetically. `LIKE` matches names without regard to
case. Syntax and description can be NULL when a function has no documentation.
Internal names beginning with `__` are omitted.

The `kind` column identifies each function as `scalar`, `aggregate`, `window`,
or `table`. Older server versions may omit table functions from this catalog.

`SHOW FUNCTIONS` lists published Python Functions instead of the built-in SQL
catalog. If your server does not recognize `SHOW BUILTIN FUNCTIONS`, ask your
administrator about upgrading.

## Vector functions

| Function | Use |
| - | - |
| `distance(a, b[, options])` | Compute vector distance. `ORDER BY distance(...) LIMIT k` can use vector-search planning. |
| `exact_distance(a, b[, options])` | Compute the same distance without the vector-search rewrite. |
| `random_vector(dimension)` | Generate a random Float32 vector. The dimension must be a positive integer literal. |
| `ivf_partition(vector[, selection => 'first' or 'random', count => n[, seed => s]])` | Group rows by an indexed vector column's IVF partition. Use as a `GROUP BY` key with an up-to-date, single-segment IVF index. |

The distance functions accept vectors of the same dimension. Their optional
options string selects `metric=l2` (the default), `metric=cosine`, `metric=dot`,
or `metric=hamming`. Dot distance is the negated dot product, so smaller values
mean more similar vectors. Hamming requires UInt8 vectors; the other metrics
accept Float16, Float32, or Float64 vectors. `euclidean` is an alias for `l2`.
For `ivf_partition`, random selection requires a nonnegative seed.

## Text similarity functions

| Function | Use |
| - | - |
| `minhash(text[, num_hashes[, shingle_size]])` | Create a MinHash signature from word shingles. Defaults: 128 hashes and 3 words per shingle. |
| `minhash_distance(a, b)` | Estimate Jaccard distance between signatures with the same configuration. |
| `minhash_lsh_keys(signature[, num_bands])` | Produce candidate-matching band keys. Defaults to 16 bands; the band count must divide the signature length. |

Start with [MinHash text deduplication](https://docs.lancedb.com/enterprise/v1-14-0/curation/dedupe.md#text) for the workflow.
See the [MinHash SQL guide](https://docs.lancedb.com/enterprise/v1-14-0/enterprise/sql-minhash.md) for configuration limits,
candidate recall, and complete batch examples.

## Blob functions

| Function | Use |
| - | - |
| `blob_descriptor(blob_column)` | Inspect a stored Blob V2 descriptor without reading its bytes. |
| `byte_length(value)` | Get the byte length of a string, binary, or Blob V2 value. |

The `length`, `octet_length`, and `bit_length` functions also support Blob V2
values.

## Table functions

Use table functions in a `FROM` clause. They return rows rather than a scalar
value per input row.

| Function | Use |
| - | - |
| `fts(table, query)` | [Full-text search](https://docs.lancedb.com/enterprise/v1-14-0/sql/dql/fts.md) using a JSON query specification. |
| `duplicate_pairs(table, column, distance_threshold => value)` | [Find duplicate pairs](https://docs.lancedb.com/enterprise/v1-14-0/sql/dql/duplicate-pairs.md): string and MinHash signature columns use MinHash; vector columns use the vector-index workflow. |
| `dedupe(table, column, distance_threshold => value)` | [Return retained rows](https://docs.lancedb.com/enterprise/v1-14-0/sql/dql/dedupe.md) after text or vector deduplication, selected by the column type. |

For deduplication calls, table and column names are string literals. The SQL
name is `dedupe`, not `dedup`. The selected column at the requested snapshot
determines whether `dedupe` and `duplicate_pairs` use text or vector matching.
Neither path modifies the source table. Both require a native table without WAL.
The vector path additionally requires a fully indexed snapshot; thresholds use
the vector metric's units. The text path requires a top-level string column or a
`FixedSizeList<UInt16>` MinHash signature column with configuration metadata,
and its threshold is estimated Jaccard distance from zero through one. Pass
`distance_threshold` by name for text calls. Text matching considers MinHash LSH
candidates and can miss pairs that meet the distance threshold; it does not
compare every pair of rows. No MinHash or vector index is required for text calls.

Use `dataset_version` to pin a snapshot. Deduplication supports a `resolution`
policy; pair enumeration does not. Text options include `num_hashes`,
`shingle_size`, and `num_bands`. See the linked references for vector options
and the [MinHash guide](https://docs.lancedb.com/enterprise/v1-14-0/enterprise/sql-minhash.md) for text limits and resolution
policies.

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT row_id_a, row_id_b, distance, dataset_version
FROM duplicate_pairs('documents', 'body', distance_threshold => 0.2);

SELECT *
FROM dedupe('documents', 'body', distance_threshold => 0.2);
```

The table above highlights Enterprise search and deduplication functions; it is
not an exhaustive list of all SQL table expressions. Consult the
[text deduplication guide](https://docs.lancedb.com/enterprise/v1-14-0/enterprise/sql-minhash.md) before choosing a resolution
policy or running a large batch.

## Published Python Functions

Use `SHOW FUNCTIONS` to discover published Functions in your current catalog
context. See [Function management](https://docs.lancedb.com/enterprise/v1-14-0/management/function.md) for publishing AI
workloads such as embedding or captioning as Functions. These are separate from the built-in functions above:

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SHOW FUNCTIONS;
SHOW FUNCTIONS IN public;
SHOW FUNCTIONS HISTORY;
SHOW FUNCTIONS WITH (limit=10);
```

Their names, inputs, and behavior depend on the Functions published to your
database. Use the published definition when choosing a Function for an agent or
an application.


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