SQL Server Error Messages - Msg 11700

Error Message

Server: Msg 11700, Level 16, State 1, Line 1
The increment for sequence object '<Sequence Name>' cannot be zero.

Causes

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

The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle or repeat as requested. Sequences, unlike identity columns, are not associated with tables.

A sequence is created independently of table objects by using the CREATE SEQUENCE statement. Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance.

The syntax of the CREATE SEQUENCE statement is as follows:

CREATE SEQUENCE <Sequence Name> AS <integer_type>
 [ START WITH <constant> ]
 [ INCREMENT BY <constant> ]
 [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
 [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
 [ CYCLE | { NO CYCLE } ]
 [ { CACHE [ <constant> ] } | { NO CACHE } ]

The INCREMENT BY argument specifies the increment (or decrement if negative) the value of the sequence object for each call to the NEXT VALUE FOR function. If the value specified in this INCREMENT BY argument is a negative value, the sequence object is descending; otherwise, it is ascending. The increment cannot be 0 and if not specified, the default value for a new sequence object is 1.

If a value of 0 is specified as the INCREMENT BY value, this error message will be raised.

CREATE SEQUENCE [dbo].[SameSequence] AS TINYINT
START WITH 1 INCREMENT BY 0

Msg 11700, Level 16, State 1, Line 2
The increment for sequence object 'dbo.SameSequence' cannot be zero.

Solution / Work Around

To avoid encountering this error message, make sure that the value specified in the INCREMENT BY is not 0. To make the sequence object return descending values, set the INCREMENT BY to a negative number. On the other hand, to make the sequence object return ascending values, set the INCREMENT BY to a positive number.

Here is an example of a sequence object that will not generate this error message:

CREATE SEQUENCE [dbo].[NewSequence] AS TINYINT
START WITH 1 INCREMENT BY 1
GO

If the intended increment for the new sequence object is 1, the INCREMENT BY argument can be left out as it will default to a value of 1:

CREATE SEQUENCE [dbo].[NewSequence] AS TINYINT
START WITH 1
GO