Categories:: Data generation functions

SEQ8¶

Returns a sequence of monotonically increasing integers, with wrap-around. Wrap-around occurs after the largest representable integer of the integer width (1, 2, 4, or 8 byte).

Important

This function uses sequences to produce a unique set of increasing integers, but does not necessarily produce a gap-free sequence. When operating on a large quantity of data, gaps can appear in a sequence. If a fully ordered, gap-free sequence is required, consider using the ROW_NUMBER window function.

For more details about sequences in Snowflake, see Using Sequences.

Syntax¶

SEQ1( [0|1] )

SEQ2( [0|1] )

SEQ4( [0|1] )

SEQ8( [0|1] )

Copy

Usage notes¶

If the optional sign argument is 0, the sequence continues at 0 after wrap-around. If the optional sign argument is 1, the sequence continues at the smallest representable number based on the given integer width.
The default sign argument is 0.

Examples¶

These are basic examples of using sequences:

SELECT seq8() FROM table(generator(rowCount => 5));

+--------+
| SEQ8() |
|--------|
|      0 |
|      1 |
|      2 |
|      3 |
|      4 |
+--------+

Copy

SELECT * FROM (SELECT seq2(0), seq1(1) FROM table(generator(rowCount => 132))) ORDER BY seq2(0) LIMIT 7 OFFSET 125;

+---------+---------+
| SEQ2(0) | SEQ1(1) |
|---------+---------|
|     125 |     125 |
|     126 |     126 |
|     127 |     127 |
|     128 |    -128 |
|     129 |    -127 |
|     130 |    -126 |
|     131 |    -125 |
+---------+---------+

Copy

This example shows how to use ROW_NUMBER to generate a sequence without gaps:

SELECT ROW_NUMBER() OVER (ORDER BY seq4()) 
    FROM TABLE(generator(rowcount => 10));
+-------------------------------------+
| ROW_NUMBER() OVER (ORDER BY SEQ4()) |
|-------------------------------------|
|                                   1 |
|                                   2 |
|                                   3 |
|                                   4 |
|                                   5 |
|                                   6 |
|                                   7 |
|                                   8 |
|                                   9 |
|                                  10 |
+-------------------------------------+

Copy