> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-home-button.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Settings at the query-level

# Query-level Session Settings

<h2 id="overview">
  Overview
</h2>

There are multiple ways to run statements with specific settings.
Settings are configured in layers, and each subsequent layer redefines the previous values of a setting.

<h2 id="order-of-priority">
  Order of priority
</h2>

The order of priority for defining a setting is:

1. Applying a setting to a user directly, or within a settings profile

   * SQL (recommended)
   * adding one or more XML or YAML files to `/etc/clickhouse-server/users.d`

2. Session settings

   * Send `SET setting=value` from the ClickHouse Cloud SQL console or
     `clickhouse client` in interactive mode. Similarly, you can use ClickHouse
     sessions in the HTTP protocol. To do this, you need to specify the
     `session_id` HTTP parameter.

3. Query settings

   * When starting `clickhouse client` in non-interactive mode, set the startup
     parameter `--setting=value`.
   * When using the HTTP API, pass CGI parameters (`URL?setting_1=value&setting_2=value...`).
   * Define settings in the
     [SETTINGS](/reference/statements/select#settings-in-select-query)
     clause of the SELECT query. The setting value is applied only to that query
     and is reset to the default or previous value after the query is executed.

<h2 id="converting-a-setting-to-its-default-value">
  Converting a setting to its default value
</h2>

If you change a setting and would like to revert it back to its default value, set the value to `DEFAULT`. The syntax looks like:

```sql theme={null}
SET setting_name = DEFAULT
```

For example, the default value of `async_insert` is `0`. Suppose you change its value to `1`:

```sql theme={null}
SET async_insert = 1;

SELECT value FROM system.settings where name='async_insert';
```

The response is:

```response theme={null}
┌─value──┐
│ 1      │
└────────┘
```

The following command sets its value back to 0:

```sql theme={null}
SET async_insert = DEFAULT;

SELECT value FROM system.settings where name='async_insert';
```

The setting is now back to its default:

```response theme={null}
┌─value───┐
│ 0       │
└─────────┘
```

<h2 id="custom_settings">
  Custom settings
</h2>

In addition to the common [settings](/reference/settings/session-settings), users can define custom settings.
Custom settings enable you to pass **session-specific parameters** that can be referenced within queries, policies, or functions. This is useful when you need to:

* Filter data based on user identity or organization
* Apply different business logic based on context
* Maintain stateful information across queries in a session

A custom setting name must begin with one of a number of predefined prefixes from a list you define.
The list of prefixes can be specified using the [`custom_settings_prefixes`](/reference/settings/server-settings/settings#custom_settings_prefixes) server setting, defined in your server configuration file.

In the example below, `SQL_` is chosen as the custom prefix:

```xml theme={null}
<custom_settings_prefixes>SQL_</custom_settings_prefixes>
```

<Note>
  In ClickHouse Cloud it is not possible to specify a custom prefix.
  All custom user settings begin with prefix `SQL_`.
</Note>

To define a custom setting use the `SET` command:

```sql theme={null}
SET SQL_a = 123;
```

To get the current value of a custom setting use `getSetting()` function:

```sql theme={null}
SELECT getSetting('SQL_a');
```

<h2 id="examples">
  Examples
</h2>

These examples all set the value of the `async_insert` setting to `1`, and
show how to examine the settings in a running system.

<h3 id="using-sql-to-apply-a-setting-to-a-user-directly">
  Using SQL to apply a setting to a user directly
</h3>

This creates the user `ingester` with the setting `async_inset = 1`:

```sql highlight={3} theme={null}
CREATE USER ingester
IDENTIFIED WITH sha256_hash BY '7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3'
SETTINGS async_insert = 1
```

<h4 id="examine-the-settings-profile-and-assignment">
  Examine the settings profile and assignment
</h4>

```sql theme={null}
SHOW ACCESS
```

```response highlight={3} theme={null}
┌─ACCESS─────────────────────────────────────────────────────────────────────────────┐
│ ...                                                                                │
│ CREATE USER ingester IDENTIFIED WITH sha256_password SETTINGS async_insert = true  │
│ ...                                                                                │
└────────────────────────────────────────────────────────────────────────────────────┘
```

<h3 id="using-sql-to-create-a-settings-profile-and-assign-to-a-user">
  Using SQL to create a settings profile and assign to a user
</h3>

This creates the profile `log_ingest` with the setting `async_inset = 1`:

```sql theme={null}
CREATE
SETTINGS PROFILE log_ingest SETTINGS async_insert = 1
```

This creates the user `ingester` and assigns the user the settings profile `log_ingest`:

```sql highlight={3} theme={null}
CREATE USER ingester
IDENTIFIED WITH sha256_hash BY '7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3'
SETTINGS PROFILE log_ingest
```

<h3 id="using-xml-to-create-a-settings-profile-and-user">
  Using XML to create a settings profile and user
</h3>

```xml title=/etc/clickhouse-server/users.d/users.xml highlight={2-6,11} theme={null}
<clickhouse>
    <profiles>
        <log_ingest>
            <async_insert>1</async_insert>
        </log_ingest>
    </profiles>

    <users>
        <ingester>
            <password_sha256_hex>7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3</password_sha256_hex>
            <profile>log_ingest</profile>
        </ingester>
        <default replace="true">
            <password_sha256_hex>7e099f39b84ea79559b3e85ea046804e63725fd1f46b37f281276aae20f86dc3</password_sha256_hex>
            <access_management>1</access_management>
            <named_collection_control>1</named_collection_control>
        </default>
    </users>
</clickhouse>
```

<h4 id="examine-the-settings-profile-and-assignment-1">
  Examine the settings profile and assignment
</h4>

```sql theme={null}
SHOW ACCESS
```

```response highlight={3,5} theme={null}
┌─ACCESS─────────────────────────────────────────────────────────────────────────────┐
│ CREATE USER default IDENTIFIED WITH sha256_password                                │
│ CREATE USER ingester IDENTIFIED WITH sha256_password SETTINGS PROFILE log_ingest   │
│ CREATE SETTINGS PROFILE default                                                    │
│ CREATE SETTINGS PROFILE log_ingest SETTINGS async_insert = true                    │
│ CREATE SETTINGS PROFILE readonly SETTINGS readonly = 1                             │
│ ...                                                                                │
└────────────────────────────────────────────────────────────────────────────────────┘
```

<h3 id="assign-a-setting-to-a-session">
  Assign a setting to a session
</h3>

```sql theme={null}
SET async_insert =1;
SELECT value FROM system.settings where name='async_insert';
```

```response theme={null}
┌─value──┐
│ 1      │
└────────┘
```

<h3 id="assign-a-setting-during-a-query">
  Assign a setting during a query
</h3>

```sql highlight={2} theme={null}
INSERT INTO YourTable
SETTINGS async_insert=1
VALUES (...)
```

<h2 id="see-also">
  See also
</h2>

* View the [Settings](/reference/settings/session-settings) page for a description of the ClickHouse settings.
* [Global server settings](/reference/settings/server-settings/settings)