Mehedi's Earth

In my Disable IDENTITY Function at INSERT article, we discussed on identity function. Now let's discuss some more interesting and unusual use of this function. Can we use negative value in identity column? The answer is "yes", we can. First re-cap identity function syntax:

Syntax

IDENTITY [ (seed , increment) ]

Arguments

• seed: Is the value that is used for the very first row loaded into the table.
• increment: Is the incremental value that is added to the identity value of the previous row that was loaded.

Now change the seed and increment arguments:

-- Drop table if it already exists.
IF OBJECT_ID('Test1', 'U') IS NOT NULL
    DROP TABLE Test1;
GO

-- Create the table
CREATE TABLE Test1(
    pk_id    int not null identity(0,-1),
    name    varchar(10) default ('Mehedi')
)
GO

-- Populate with 100 sample data
INSERT INTO Test1 default values
GO 100

-- Examine the data
SELECT * FROM Test1
GO

Output