Tuesday, February 21, 2017

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;

  1. -- creating sequence  
  2. CREATE SEQUENCE [dbo].[OrderSequence]  
  3. AS int  
  4. START WITH 1   
  5. INCREMENT BY 1   
  6. MAXVALUE 2   
  7. CYCLE  
  8. GO  
  9.   
  10. -- requesting numbers three times  
  11. SELECT (NEXT VALUE FOR   dbo.OrderSequence) AS OrderSequence  
  12. 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.

No comments: