---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://ydb.tech/docs/en/yql/reference/syntax/create_table/with.md?version=v25.4
  - https://ydb.tech/docs/ru/yql/reference/syntax/create_table/with.md?version=v25.4
  - href: en/yql/reference/syntax/create_table/with.md
    type: text/markdown
    title: Markdown version
  - href: ../../../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/yql/reference/syntax/create_table/with.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Additional parameters (WITH)

You can also specify a number of YDB-specific parameters for the table. When you create a table, those parameters are listed in the ```WITH``` clause:

```yql
CREATE TABLE table_name (...)
WITH (
    key1 = value1,
    key2 = value2,
    ...
)
```

Here, `key` is the name of the parameter and `value` is its value.

The list of allowable parameter names and their values is provided on the table description page [YDB](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=v25.4).

For example, such a query will create a string table with automatic partitioning enabled based on partition size and a preferred size of each partition being 512 megabytes:

```yql
CREATE TABLE my_table (
    id Uint64,
    title Utf8,
    PRIMARY KEY (id)
)
WITH (
    AUTO_PARTITIONING_BY_SIZE = ENABLED,
    AUTO_PARTITIONING_PARTITION_SIZE_MB = 512
);
```


A colum-oriented table is created by specifying the parameter `STORE = COLUMN` in the `WITH` clause:

```sql
 CREATE TABLE table_name (
    a Uint64 NOT NULL,
    b Timestamp NOT NULL,
    c Float,
    PRIMARY KEY (a, b)
  )
  PARTITION BY HASH(b)
  WITH (
    STORE = COLUMN
  );
```

The properties and capabilities of columnar tables are described in the article [Table](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=v25.4), and the specifics of their creation through YQL are described on the page [CREATE TABLE](https://ydb.tech/docs/en/yql/reference/syntax/create_table/index.md?version=v25.4).

## Time to Live (TTL) {#time-to-live}

The TTL (Time to Live) — the lifespan of a row — can be specified in the WITH clause for row-based and columnar tables. [TTL](https://ydb.tech/docs/en/concepts/ttl.md?version=v25.4) automatically deletes rows or evicts them to external storage when the specified number of seconds has passed since the time recorded in the TTL column. TTL can be specified when creating row-based and columnar tables or added later using the `ALTER TABLE` command only for row-based tables.

The short form of the TTL value for specifying the time to delete rows:

```yql
Interval("<literal>") ON column [AS <unit>]
```

The general form of the TTL value:

```yql
Interval("<literal1>") action1, ..., Interval("<literalN>") actionN ON column [AS <unit>]
```

* `action` — the action performed when the TTL expression triggers. Allowed values:
    * `DELETE` — delete the row;
    * `TO EXTERNAL DATA SOURCE <path>` — evict the row to external storage specified by the [external data source](https://ydb.tech/docs/en/concepts/datamodel/external_data_source.md?version=v25.4) at the path `<path>`.
* `<unit>` — the unit of measurement, specified only for columns with a [numeric type](https://ydb.tech/docs/en/concepts/ttl.md?version=v25.4#restrictions):
    * `SECONDS`;
    * `MILLISECONDS`;
    * `MICROSECONDS`;
    * `NANOSECONDS`.

Example of creating a row-oriented and column-oriented tables with TTL:

{% list tabs %}

- Creating row-oriented table with TTL

    ```sql
    CREATE TABLE my_table (
        id Uint64,
        title Utf8,
        expire_at Timestamp,
        PRIMARY KEY (id)
    )
    WITH (
        TTL = Interval("PT0S") ON expire_at
    );
    ```

- Creating column-oriented table with TTL

    ```sql
    CREATE TABLE table_name (
        a Uint64 NOT NULL,
        b Timestamp NOT NULL,
        c Float,
        PRIMARY KEY (a, b)
    )
    PARTITION BY HASH(b)
    WITH (
        STORE = COLUMN,
        TTL = Interval("PT0S") ON b
    );
    ```

{% endlist %}

Example of creating a column-oriented table with eviction to external storage:

<!-- source: en/_includes/not_allow_for_oltp_note.md -->
{% note warning %}

<!-- source: en/_includes/not_allow_for_oltp_text.md -->
Supported only for [column-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=v25.4#column-oriented-tables) tables. Support for [row-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=v25.4#row-oriented-tables) tables is currently under development.
<!-- endsource: en/_includes/not_allow_for_oltp_text.md -->

{% endnote %}
<!-- endsource: en/_includes/not_allow_for_oltp_note.md -->

```yql
CREATE TABLE table_name (
    a Uint64 NOT NULL,
    b Timestamp NOT NULL,
    c Float,
    PRIMARY KEY (a, b)
)
PARTITION BY HASH(b)
WITH (
    STORE = COLUMN,
    TTL =
        Interval("PT1D") TO EXTERNAL DATA SOURCE `/Root/s3`,
        Interval("P2D") DELETE
    ON b
);
```