Min & Max Value in SQL Server SEQUENCE

A SEQUENCE in SQL Server is a database object that generates numeric values. It works similar to Identity, but it is much more powerful and flexible. One of the most important concepts in SEQUENCE is Min Value and Max Value. The Min and Max value of a SEQUENCE are decided by its data type. So whichever datatype our choose, its numeric range automatically becomes the minimum and maximum limit of the SEQUENCE.

SEQUENCE datatype

Data Type	Min	MAX
INT	–2,147,483,648	2,147,483,647
BIGINT	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
SMALLINT	-32,768	32,767
TINYINT	0	255
DECIMAL	-10^38	10^38

If We do not specify MINVALUE and MAXVALUE, SQL Server automatically uses the lowest and highest value of the datatype as default limits.

For example

CREATE SEQUENCE MySeq AS INT START WITH 1 INCREMENT BY 1;

Here data type is Int and we are not declaring the min and max value so by default min value is -2,147,483,648 and max value is 2,147,483,647but here we are starting from 1 so min value is 1 and max value is 2,147,483,647.

See the below demo for min and max size

CREATE SEQUENCE SeqLimit AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10;

Here we are setting min 1 and max value 10.

I have run 10 times of this sequence.

Let’s running again

Oooh! Now we get an error.

This sequence will start from 1 and it can go up to 10 only and we have reached it after that we are getting this error.

By default, SEQUENCE is NO CYCLE.

That means once it reaches the maximum limit, SQL Server will stop generating numbers and it will throw an error: ‘The sequence object has reached its maximum value.’ See above.

If We want the sequence to restart automatically after reaching the maximum value, then we must use the CYCLE option.

Let’s create the Sequence with CYCLE.

CREATE SEQUENCE SeqLimit_withCycle AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10 CYCLE;

Running 10 times and see the value

After reaching the max value again we are running this sequence.

Now it started with 1.

So when we say CYCLE, it means the sequence wraps back to the minimum value and continues.

To check the current sequence value and limits

SELECT name, current_value,        minimum_value, maximum_value,        increment, is_cycling FROM sys.sequences;

The Min and Max value of a SEQUENCE in SQL Server are determined by its data type. If we don’t define them, SQL Server automatically uses datatype limits. Once the sequence reaches its maximum value, it either throws an error or restarts depending on whether CYCLE is enabled.