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)