# Table functions in BigQuery

> A table function is a parameterized view, a stored query you call in FROM with arguments. Constant arguments prune partitions and unused columns aren't read; an argument computed by a subquery reads the whole table. There are no temporary table functions.

Source: https://docs.querylab.io/bigquery/table-functions/

Updated: 2026-10-14

A table function (also called a table-valued function, or TVF) is a stored `SELECT` with parameters. You call it in `FROM` like a table, and pass the values the query needs. Think of it as a view that takes arguments: one definition of "sales for a day" or "orders for a customer" that every report can reuse, instead of each copying the query and the filter.

## Creating and calling one

```sql
CREATE OR REPLACE TABLE FUNCTION mydataset.sales_on(d DATE)
AS (
  SELECT id, store, amount
  FROM mydataset.sales
  WHERE day = d
);

SELECT store, SUM(amount) AS total
FROM mydataset.sales_on('2026-03-02')
GROUP BY store;
```

In Querylab.io, a dataset's SQL Templates menu has a Table Function skeleton to start from ([DDL operations](https://docs.querylab.io/schema-browser/ddl-operations/#dataset-templates)).

The result can be joined, filtered and aggregated like any table. A string literal like `'2026-03-02'` is converted to the `DATE` parameter, the same way it would be in a comparison.

Parameters can be `ANY TYPE`, and the body can use any `SELECT` syntax, including CTEs, joins and window functions. `RETURNS TABLE<id INT64, ...>` after the parameters is optional; without it, BigQuery works out the output columns from the query.

### No temporary table functions

Unlike scalar [user-defined functions](https://docs.querylab.io/bigquery/user-defined-functions/), a table function can't be temporary. `CREATE TEMP TABLE FUNCTION` fails with "CREATE TEMPORARY TABLE FUNCTION statements are not currently supported". Every table function lives in a dataset.

## What a call costs

A call is billed like the query it expands to, and BigQuery optimizes the function body together with the calling query. We measured this with a function over a 2.25 MB table (`day`, `id`, `note`) partitioned by day into 10 partitions:

| Call                                                   | Bytes processed                   |
| ------------------------------------------------------ | --------------------------------- |
| `SELECT id, note` from the table directly              | 2.1 MB, every partition           |
| `SELECT * FROM sales_on('2026-01-03')`                 | 230 KB, one partition             |
| `SELECT id FROM sales_on('2026-01-03')`                | 31 KB, one partition, two columns |
| `SELECT * FROM sales_on((SELECT MAX(day) FROM sales))` | 2.25 MB, the whole table          |

So:

- A constant argument used in the body's `WHERE` on the partitioning column prunes partitions, the same as writing the filter by hand.
- Columns the caller doesn't select aren't read, even if the function returns them. Selecting only `id` read `id` plus the `day` column used by the filter.
- An argument computed by a subquery isn't known when the query is planned, so nothing is pruned. If you need "the latest day", compute it first into a [script variable](https://docs.querylab.io/bigquery/scripting/) and pass the variable.

## Table functions vs views

|                                                   | View                                       | Table function                             |
| ------------------------------------------------- | ------------------------------------------ | ------------------------------------------ |
| Parameters                                        | none; callers add `WHERE`                  | yes                                        |
| Called as                                         | `FROM mydataset.v`                         | `FROM mydataset.f(args)`                   |
| Filter on a partition column                      | pruned when the caller's filter reaches it | pruned when a constant argument reaches it |
| Can be authorized to read tables the caller can't | yes, an authorized view                    | yes, an authorized routine                 |

Use a view when callers filter in different ways, and a table function when every call needs the same filter with a different value and you want to make it impossible to forget. See [Views](https://docs.querylab.io/bigquery/views/) and [Authorized views](https://docs.querylab.io/bigquery/authorized-views/).

## Built-in table functions

BigQuery also has its own table functions. A few that are specific to it:

- `APPENDS(TABLE t, start, end)` and `CHANGES(TABLE t, start, end)` return the rows inserted, or inserted and changed, in a time range. See [Change data capture](https://docs.querylab.io/bigquery/change-data-capture/).
- `VECTOR_SEARCH(...)` finds the nearest rows by embedding. See [Vector search](https://docs.querylab.io/bigquery/vector-search/).
- `EXTERNAL_QUERY(connection, sql)` runs a query in Cloud SQL, Spanner or AlloyDB and returns its result. See [Federated queries](https://docs.querylab.io/bigquery/federated-queries/).
- `ML.PREDICT`, `ML.EVALUATE`, `AI.GENERATE_TABLE` and the other `ML.` and `AI.` functions return tables. See [BigQuery ML](https://docs.querylab.io/bigquery/bigquery-ml/).
- `GAP_FILL` fills missing points in a time series, and `RANGE_SESSIONIZE` groups overlapping `RANGE` values into sessions.

## Permissions

Callers need `bigquery.routines.get` on the function and read access to the tables it queries, unless the function is an authorized routine, which reads the tables with its own access.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Table functions](https://cloud.google.com/bigquery/docs/table-functions)

- [DDL, CREATE TABLE FUNCTION](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_function_statement)

- [Built-in table functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions-built-in)