I just had 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 negative value. He did not believe it. I quickly come with example and he was surprised to see it.
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]
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
(
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 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth'
GO
SELECT *
FROM dbo.IdentityNegative
GO
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.
Read more: Journey to SQL Authority with Pinal Dave
GO
Now let us see the resultset. Here we have specified negative identity Seed value as well negative Increment Interval.
Read more: Journey to SQL Authority with Pinal Dave