When creating a sequence object in SQL Server, it’s common to set various properties such as the start value, the increment, and even things like the maximum and minimum values for the sequence. But it is also possible to create a sequence without setting any of these properties.

Example

Here’s an example of creating a sequence object using the default values:

CREATE SEQUENCE DefaultSequence1;

That’s all.

That example created a sequence called DefaultSequence1 using the default values. Whether these values are suitable for our needs is another story, but we can always check the values with the sys.sequences system catalog view:

SELECT
    current_value,
    start_value,
    increment,
    minimum_value,
    maximum_value,
    is_cycling,
    is_cached,
    TYPE_NAME(system_type_id) AS system_type,
    TYPE_NAME(user_type_id) AS user_type,
    [precision],
    scale
FROM sys.sequences
WHERE name = 'DefaultSequence1';

Result:

current_value         start_value           increment  minimum_value         maximum_value        is_cycling  is_cached  system_type  user_type  precision  scale
--------------------  --------------------  ---------  --------------------  -------------------  ----------  ---------  -----------  ---------  ---------  -----
-9223372036854775808  -9223372036854775808  1          -9223372036854775808  9223372036854775807  false       true       bigint       bigint     19         0    

Data Type Affects the Default Values

The default data type for a sequence is bigint. We can see from the above example that the minimum_value, maximum_value and others reflect the bigint data type.

However, we also have the option of specifying the data type when creating the sequence. When we do this, the default value of some properties will be different from those above. That’s because they will need to adhere to the limitations of the specified data type.

For example, a tinyint sequence will need to fit within the 0 to 255 range, a smallint within the -32,768 to 32,767 range, and so on.

Here’s an example of creating a sequence with a smallint:

CREATE SEQUENCE DefaultSequence2 AS smallint;

All we did was add the AS argument with the data type.

Now let’s check the sys.sequence view for the properties of this sequence object:

SELECT
    current_value,
    start_value,
    increment,
    minimum_value,
    maximum_value,
    is_cycling,
    is_cached,
    TYPE_NAME(system_type_id) AS system_type,
    TYPE_NAME(user_type_id) AS user_type,
    [precision],
    scale
FROM sys.sequences
WHERE name = 'DefaultSequence2';

Result:

current_value  start_value  increment  minimum_value  maximum_value  is_cycling  is_cached  system_type  user_type  precision  scale
-------------  -----------  ---------  -------------  -------------  ----------  ---------  -----------  ---------  ---------  -----
-32768         -32768       1          -32768         32767          false       true       smallint     smallint   5          0    

The current_value, start_value, minimum_value, and maximum_value are different from the sequence we created in the previous example. That’s because minimum_value, and maximum_value have to fit within limits of the smallint type (which is -32,768 to 32,767).

Note that the default start value for a new sequence object is the minimum value for an ascending sequence object and the maximum value for a descending sequence object. In our case, the sequence object is ascending, and so the default start value is the lower limit of the smallint data type, which is -32,768.