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.
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
INSERT INTO dbo.IdentityNegative (Firstcol)
DROP TABLE dbo.IdentityNegative
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 negative value? If yes, can you come up with proper business need of the same?
Reference: Pinal Dave (http://blog.sqlauthority.com)