T-SQL auto increment identity?

Question

I have a SQL Server table defined as

CREATE TABLE [dbo].[Products] 
(
    [ProductId]   INT            IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (50)  NOT NULL,
    [Description] NVARCHAR (400) NOT NULL,
    [Category]    NVARCHAR (50)  NOT NULL,
    [Price]       DECIMAL (18)   NOT NULL,
    PRIMARY KEY CLUSTERED ([ProductId] ASC)
);

Now my problem is that I have previously stored 7 items in the table with ID starting from 1 to 7 in sequence and when I add a new product in the table the ID automatically gets a random value like 1016, then for another new item it gets value 1017 and so on. How can I set ID value so that it automatically detect last stored ID and increment 1 in that value automatically?

this bound to be duplicate: for example: [Reset Identity Seed](http://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server/). — s952163, Jul 24 '16 at 13:39
Possible duplicate of [Identity increment is jumping in SQL Server database](http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database) — Ivan Starostin, Jul 24 '16 at 16:19
I used magic `-T272` in startup parameters from answer to [Identity increment is jumping in SQL Server database](stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database) — Alex Kudryashev, Jul 24 '16 at 17:05

score 2 · Accepted Answer · answered Jul 24 '16 at 14:04

It shouldn't really matter what values your identity column is generating.

If your code or application depends on values generated from the Identity column then you should really fix that logic.

According to MSDN Identity values are guaranteed to be incremental and sequential but there is no guarantee if they will be consecutive values. Especially in SQL Server 2012 it tends to take big jumps like from 100 to 1000.

Anyway to Reseed an Identity column to next available highest value you can do the following.

-- Reseed to any lowest value say 0
DBCC CHECKIDENT ('[dbo].[Products]', RESEED , 0)
GO

-- Execute the same command but this time without any seed value 
DBCC CHECKIDENT ('[dbo].[Products]', RESEED)
GO

This will reseed your identity column to next highest available value in the identity column.

score 1 · Answer 2 · answered Jul 24 '16 at 13:55

You can reset the identity seed like this:

DECLARE @cmd VARCHAR(MAX)
SELECT 'DBCC CHECKIDENT (''[dbo].[Products]'', RESEED, '+(CAST(MAX(id)+1 AS    VARCHAR(MAX)))+');' FROM [dbo].[Products]
-- PRINT @cmd
EXEC(@cmd)

You have to consider that if you need to have a clean sequence of id's without any holes, IDENTITY will not be able to give you this. If a transaction where rows are inserted would be rolled back, then the IDENTITY will stay incremented and new holes will appear.

score 1 · Answer 3 · answered Jul 24 '16 at 15:45

There are Two problems that I see with the way you have created your table and what I understand your requirement is:

Identity Column should not be used if you need serially incrementing rows with no gaps. It does not guarantee that. And trying to RE-SEED it to plug gaps is a bad idea. Better to ask yourselves do you really need it to be sequential. If the answer is Yes then you have to implement it programatically in the Stored Procedure that handles the INSERT for this table. I personally do not see a reason. Another important thing to consider is if this table is referenced to other tables using the ID column.
You are using the ID column as also the PK column and I dont see any other Unique Index created on the Natural Columns. So SQL Server wont prevent you from inserting Two or more Products with the same name (and indeed every column in that table being identical ) with different id's. You are forced to handle this programatically. IF you relying on this table to have one row per product then the schema itself isnt stopping anyone from inserting duplicates. An easy fix is to create a unique index on the business keys that make the row unique ( Name + Category ).

T-SQL auto increment identity?

3 Answers3