3

At my job we have a lot of objects that we track versions of. We are tentatively looking at creating a rather large number of sequence objects in order to keep track of these version. My concern is that sql server may have a max number of these that we can create, but I am unable to find any such documentation.

So, is there a max number of sequences that can be created in SQL Server 2014 and 2016?

3

What is a Sequence?

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created.

MSDN on Sequence definition reference.

Ok, it's a schema bound user defined object. What does SQL say about how many User Defined Objects we can have?

Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

MSDN on maximum UDF reference.

Summary answer: You can have 2,147,483,647 objects in a database. One of those objects referenced would be Sequence Objects which Sequences fall into that category. This is not explicitly stated, but it can be contrived from the wording "... the sum of the number of all objects in a database cannot exceed 2,147,483,647."

| improve this answer | |
1

Complementing to Shaulinator's answer :

The max value maximum_value for sys.sequences is the maximum value supported by the sequence object's data type.

if you use int as object's datatype then max value is 2,147,483,647.

if you use bigint as object's datatype then max value is 9,223,372,036,854,775,807

You should monitor is_exhausted in sys.sequences table and make sure it is not 1.

0 indicates that more values can be generated from the sequence. 1 indicates that the sequence object has reached the MAXVALUE parameter and the sequence is not set to CYCLE. The NEXT VALUE FOR function returns an error until the sequence is restarted by using ALTER SEQUENCE.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.