SQL Server Sequence suddenly starts with -2147483648

Have you faced this? Assume that you have created a Sequence object for generating values sequentially for tables which data type is set as int, and suddenly it shows the next number as -2147483648.

See below code;

-- creating sequence
CREATE SEQUENCE [dbo].[OrderSequence]
AS int
START WITH 1 
INCREMENT BY 1 
MAXVALUE 2 
CYCLE
GO

-- requesting numbers three times
SELECT (NEXT VALUE FOR   dbo.OrderSequence) AS OrderSequence
GO 3

What could be the reason? If you analyze the code written above, you can easily find the issue. I have not used MINVALUE property when creating the Sequence, hence it takes the lowest value of the data type set for the Sequence, which is -2147483648 for int data type. You may experience the same, if so, check and see whether the MINVALUE has been set or not.

My Application needs a new sequence number to the table without inserting the record

This is something I discussed with one of my colleagues, it was all about obtaining the next sequence number from Identity type column without inserting the record and use it for other entities inside the application, and later submit all to the database for insertion. However, if this is the requirement, it is not possible with Identity because the next value cannot be obtained from Identity-Column without inserting the record. This issue can be seen often with Entity Framwork enabled applications.

What would be the best way of handling this? Traditional way of handling this is, maintaining another table that holds key values for tables and using it for issuing next available values. It needs additional coding, extra effort and it introduces unexpected locking issues too. Modern way and best way of handling this is, using SEQUENCE object which was introduced with SQL Server 2012.

The SEQUENCE is a user-defined schema-bound object that generates sequence numbers based on the setting added. Multiple properties can be set with this such as Starting number, interval, and order. Unlike Identity, defined SEQUENCE object can be used with any table because there is no link between SEQUENCE object and a table. Although one SEQUENCE object can be used with all tables, it is advisable to create multiple SEQUENCE objects, one per table because it makes the maintainability easy and minimizes gaps between numbers added to tables.

Here is the way of creating a SEQUENCE object.

CREATE SEQUENCE Sales.OrderSequence
    START WITH 1
    INCREMENT BY 1;
GO

Once created, application can request for the next number which can be used as key of the table, calling below command;

SELECT NEXT VALUE FOR Sales.OrderSequence;

For more info on SEQUENCE object, refer: https://msdn.microsoft.com/en-us/library/ff878091.aspx