Other functions

You can use setting [`allow_deprecated_error_prone_window_functions`](/docs/operations/settings/settings#allow_deprecated_error_prone_window_functions) to allow usage of this function.

The result of the function depends on the affected data blocks and the order of data in the block.
The order of rows during calculation of `runningDifference()` can differ from the order of rows returned to the user.
To prevent that you can create a subquery with [`ORDER BY`](/docs/sql-reference/statements/select/order-by) and call the function from outside the subquery.
Please note that the block size affects the result.
The internal state of `runningDifference` state is reset for each new block.

**Syntax**

```
runningDifference(x)
```

**Arguments**

- `x` — Column for which to calculate the running difference. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the difference between consecutive values, with 0 for the first row.

**Examples**

**Usage example**

```
SELECT
    EventID,
    EventTime,
    runningDifference(EventTime) AS delta
FROM
(
    SELECT
        EventID,
        EventTime
    FROM events
    WHERE EventDate = '2025-11-24'
    ORDER BY EventTime ASC
    LIMIT 5
);
```

```
┌─EventID─┬───────────EventTime─┬─delta─┐
│    1106 │ 2025-11-24 00:00:04 │     0 │
│    1107 │ 2025-11-24 00:00:05 │     1 │
│    1108 │ 2025-11-24 00:00:05 │     0 │
│    1109 │ 2025-11-24 00:00:09 │     4 │
│    1110 │ 2025-11-24 00:00:10 │     1 │
└─────────┴─────────────────────┴───────┘
```

**Block size impact example**

```
SELECT
    number,
    runningDifference(number + 1) AS diff
FROM numbers(100000)
WHERE diff != 1;
```

```
┌─number─┬─diff─┐
│      0 │    0 │
└────────┴──────┘
┌─number─┬─diff─┐
│  65536 │    0 │
└────────┴──────┘
```

## runningDifferenceStartingWithFirstValue[​](#runningDifferenceStartingWithFirstValue "Direct link to runningDifferenceStartingWithFirstValue")

Introduced in: v1.1.0

Calculates the difference between consecutive row values in a data block, but unlike [`runningDifference`](#runningDifference), it returns the actual value of the first row instead of `0`.

Deprecated

Only returns differences inside the currently processed data block.
Because of this error-prone behavior, the function is deprecated.
It is advised to use [window functions](/docs/sql-reference/window-functions) instead.

You can use setting `allow_deprecated_error_prone_window_functions` to allow usage of this function.

**Syntax**

```
runningDifferenceStartingWithFirstValue(x)
```

**Arguments**

- `x` — Column for which to calculate the running difference. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the difference between consecutive values, with the first row's value for the first row. [`Any`](/docs/sql-reference/data-types)

**Examples**

**Usage example**

```
SELECT
    number,
    runningDifferenceStartingWithFirstValue(number) AS diff
FROM numbers(5);
```

```
┌─number─┬─diff─┐
│      0 │    0 │
│      1 │    1 │
│      2 │    1 │
│      3 │    1 │
│      4 │    1 │
└────────┴──────┘
```

## serverUUID[​](#serverUUID "Direct link to serverUUID")

Introduced in: v20.1.0

Returns the random and unique UUID (v4) generated when the server is first started.
The UUID is persisted, i.e. the second, third, etc. server start return the same UUID.

**Syntax**

```
serverUUID()
```

**Arguments**

- None.

**Returned value**

Returns the random UUID of the server. [`UUID`](/docs/sql-reference/data-types/uuid)

**Examples**

**Usage example**

```
SELECT serverUUID();
```

```
┌─serverUUID()─────────────────────────────┐
│ 7ccc9260-000d-4d5c-a843-5459abaabb5f     │
└──────────────────────────────────────────┘
```

## shardCount[​](#shardCount "Direct link to shardCount")

Introduced in: v21.9.0

Returns the total number of shards for a distributed query.
If a query is not distributed then constant value `0` is returned.

**Syntax**

```
shardCount()
```

**Arguments**

- None.

**Returned value**

Returns the total number of shards or `0`. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
-- See shardNum() example above which also demonstrates shardCount()
CREATE TABLE shard_count_example (dummy UInt8)
ENGINE=Distributed(test_cluster_two_shards_localhost, system, one, dummy);
SELECT shardCount() FROM shard_count_example;
```

```
┌─shardCount()─┐
│            2 │
│            2 │
└──────────────┘
```

## shardNum[​](#shardNum "Direct link to shardNum")

Introduced in: v21.9.0

Returns the index of a shard which processes a part of data in a distributed query.
Indices begin from `1`.
If a query is not distributed then a constant value `0` is returned.

**Syntax**

```
shardNum()
```

**Arguments**

- None.

**Returned value**

Returns the shard index or a constant `0`. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
CREATE TABLE shard_num_example (dummy UInt8)
ENGINE=Distributed(test_cluster_two_shards_localhost, system, one, dummy);
SELECT dummy, shardNum(), shardCount() FROM shard_num_example;
```

```
┌─dummy─┬─shardNum()─┬─shardCount()─┐
│     0 │          1 │            2 │
│     0 │          2 │            2 │
└───────┴────────────┴──────────────┘
```

## showCertificate[​](#showCertificate "Direct link to showCertificate")

Introduced in: v22.6.0

Shows information about the current server's Secure Sockets Layer (SSL) certificate if it has been configured.
See [Configuring TLS](/docs/guides/sre/tls/configuring-tls) for more information on how to configure ClickHouse to use OpenSSL certificates to validate connections.

**Syntax**

```
showCertificate()
```

**Arguments**

- None.

**Returned value**

Returns map of key-value pairs relating to the configured SSL certificate. [`Map(String, String)`](/docs/sql-reference/data-types/map)

**Examples**

**Usage example**

```
SELECT showCertificate() FORMAT LineAsString;
```

```
{'version':'1','serial_number':'2D9071D64530052D48308473922C7ADAFA85D6C5','signature_algo':'sha256WithRSAEncryption','issuer':'/CN=marsnet.local CA','not_before':'May  7 17:01:21 2024 GMT','not_after':'May  7 17:01:21 2025 GMT','subject':'/CN=chnode1','pkey_algo':'rsaEncryption'}
```

## sleep[​](#sleep "Direct link to sleep")

Introduced in: v1.1.0

Pauses the execution of a query by the specified number of seconds.
The function is primarily used for testing and debugging purposes.

The `sleep()` function should generally not be used in production environments, as it can negatively impact query performance and system responsiveness.
However, it can be useful in the following scenarios:

1. **Testing**: When testing or benchmarking ClickHouse, you may want to simulate delays or introduce pauses to observe how the system behaves under certain conditions.
2. **Debugging**: If you need to examine the state of the system or the execution of a query at a specific point in time, you can use `sleep()` to introduce a pause, allowing you to inspect or collect relevant information.
3. **Simulation**: In some cases, you may want to simulate real-world scenarios where delays or pauses occur, such as network latency or external system dependencies.

Note

It's important to use the `sleep()` function judiciously and only when necessary, as it can potentially impact the overall performance and responsiveness of your ClickHouse system.

For security reasons, the function can only be executed in the default user profile (with `allow_sleep` enabled).

**Syntax**

```
sleep(seconds)
```

**Arguments**

- `seconds` — The number of seconds to pause the query execution to a maximum of 3 seconds. It can be a floating-point value to specify fractional seconds. [`const UInt*`](/docs/sql-reference/data-types/int-uint) or [`const Float*`](/docs/sql-reference/data-types/float)

**Returned value**

Returns `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
-- This query will pause for 2 seconds before completing.
-- During this time, no results will be returned, and the query will appear to be hanging or unresponsive.
SELECT sleep(2);
```