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.

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.

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)

SQL Function, SQL Identity, SQL Scripts, SQL Server
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
Exit mobile version