By default, SQL Server sets its own minimum and maximum values for sequence objects based on the data type of the sequence object. Assuming we don’t set the data type for the sequence, these min/max values are based on the bigint data type (because that’s the default data type for sequence objects in SQL Server).

However, we can also set our own minimum and maximum values for our sequences. We can do this either explicitly (by setting the MAXVALUE and MINVALUE properties) or implicitly (by setting the data type).

Option 1: Set the MINVALUE and/or MAXVALUE Properties

Here’s an example of explicitly setting the MAXVALUE and MINVALUE properties:

CREATE SEQUENCE Sequence1
    MINVALUE -200
    MAXVALUE 200;

Here, I created a sequence object that has a minimum value of -200 and a maximum value of 200. All other properties use their default values.

The default start value is the minimum value for an ascending sequence object and the maximum value for a descending sequence object. Therefore in our example, the default start value will be -200.

The default increment is 1. Therefore, our sequence will have an increment of 1.

Let’s use the sys.sequences system catalog view to check these properties:

SELECT
name,
    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 = 'Sequence1';

Result:

name       current_value  start_value  increment  minimum_value  maximum_value  is_cycling  is_cached  system_type  user_type  precision  scale

---------  -------------  -----------  ---------  -------------  -------------  ----------  ---------  -----------  ---------  ---------  -----

Sequence1  -200           -200         1          -200           200            false       true       bigint       bigint     19         0

As expected, we can see that the value at start_value is the value we specified with the MINVALUE property. It’s the same with the current_value column.

This option allows us to set min/max values that are different to the min/max values of the data type. However, as alluded to, if we omit the MINVALUE and MAXVALUE arguments, the data type will dictate the min/max values.

Option 2: Change the Data Type

Another way to set the minimum and maximum values is to set the data type. The min/max values are dictated by the limitations of the data type. The default minimum value is the minimum value of the data type of the sequence object, and the default maximum value is the maximum value of the data type.

For example, if we set the data type to smallint, the range of the sequence will be -32,768 to 32,767, and so the default max/min values will be at those two extremes.

Example:

CREATE SEQUENCE Sequence2 AS smallint;

This sets the sequence object with a smallint data type.

Let’s take a look at its properties:

SELECT
name,
    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 = 'Sequence2';

Result:

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

Here, we can see that the minimum_value has a default value of -32768 (as does the start_value), and the maximum_value has a default value of 32767.