SQL SERVER – Negative Identity Seed Value and Negative Increment Interval

I just had an interesting conversation with one of my friend who said identity value can not start from Zero. I told him that it can even start from a negative value. He did not believe it. I quickly come with example and he was surprised to see it. Let us learn today about Negative Identity Seed Value and Negative Increment Interval.

SQL SERVER - Negative Identity Seed Value and Negative Increment Interval Negative-Increment-800x373

USE [AdventureWorks]
GO
IF EXISTS (SELECT * FROM sys.objects 
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[IdentityNegative]') 
AND TYPE IN (N'U'))
DROP TABLE [dbo].[IdentityNegative]
GO
CREATE TABLE dbo.IdentityNegative
(
ID INT NOT NULL IDENTITY (-100, -1),
Firstcol VARCHAR(100) NULL
)
GO
INSERT INTO dbo.IdentityNegative (Firstcol)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth'
GO
SELECT *
FROM dbo.IdentityNegative
GO
DROP TABLE dbo.IdentityNegative
GO

Now let us see the resultset. Here we have specified negative identity Seed value as well negative Increment Interval.

SQL SERVER - Negative Identity Seed Value and Negative Increment Interval negativeidentity

Did you know that Identity Value can have a negative value? If yes, can you come up with the proper business need of the same? You can subscribe to me on my YouTube Channel.

Here are a few additional blog post related to this topic:

SQL SERVER – Having Two Identity Columns for A Single Table
In yesterday’s session, a DBA asked an interesting question that if it is possible to have two identity columns for a single table. Yes and No.

SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column
today’s goal is to understand what is the Last Page Insert PAGELATCH_EX Contention Due to Primary Key. Let us start understanding it together.

SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE
SQL Server 2017 has a wonderful feature which fixes a very old issue which DBA and developers have been struggling for a while.

SQL SERVER – Jump in Identity Column After Restart
The SQL Product team makes a continuous change in the product which sometimes causes unpredictable behaviours in the SQL Server engine.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Download SQL Server 2008 Interview Questions and Answers Complete List
Next Post
SQL SERVER – Function to Retrieve First Word of Sentence – String Operation

Related Posts

26 Comments. Leave new

  • I have a requirement to allow the user to create his own folders alongside the predefined ones and then add docs to them. Am trying to keep the docs in one table with a foriegn key to a folder id which comes from either the Folder table or the lookup table(the lookup tbale id would be common for all users).

    Would it be a good idea to have the predefined folders in the lookup to have a -ve id and the ones created by the user to have a +ve id ? that way the folder id’s would be unique to each user.

    Reply
  • Jeremy Holovacs
    May 16, 2018 11:57 pm

    Yes, you can do it… but good grief, don’t do it.

    Reply
  • I have an identity INT column where the table has exceeded the top range of int value, I can reseed the table to use negative numbers as a short term fix, however, can I change the increment value form +1 to -1 so I can start the column from 0 and go down or do I need to reseed to a very negative value and keep existing increment?

    Reply

Leave a Reply

Menu