2

Step 1: Create Table

CREATE TABLE [dbo].[AAA](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NOT NULL,
[last] [nchar](10) NOT NULL)

Step 2: Insert Correct Value

insert into aaa values ('lkj', 'lkj')

This will Generate ID - 1

Step 3: Try to Insert Incorrect Value

insert into aaa values ('lkj', NULL)

This will error and no record will be inserted

Step 4:

insert into aaa values ('lkj', 'lkj')

This will insert again, But ID generated will be - 3 (Not 2) Because ID 2 was generated at Step 3 but no record was inserted.

I want that when I next enter any record ID generated should be 2 and not 3. I have searched and found solution for MySQL below, but I want it in SQL Server:

alter table tablename auto_increment =1

OR

alter table tablename auto_increment =#

Also there is another solution I found...

declare @max int;
select @max = max(key) from table;
dbcc checkident(table,reseed,@max)

This code is good and it works. But I have to execute this every time there is a skipped ID... Isn't there a permanent fix for this problem? I want something like "execute it once and then forget about it..." I am facing this problem on production server so I want a permanent solution. I am using entity framework for inserting data so i cant use this query every time.

Please let me know any way to fix it. Either in SQL Server 2012 or in Entity Framework 6. Thanks in Advance.

Devang
  • 299
  • 2
  • 4
  • 14
  • 10
    why does this matter to you? the incremented id should only be for identification – Zach Spencer Jan 15 '15 at 21:08
  • 10
    The identity property in sql-server is designed to track the number of insert attempts. Gaps are normal behavior and should be expected. Even if you manage to get the numbers inserted sequentially you are going to end up with gaps when rows get deleted. If you are using 2012+ you might take a look at sequences. It will make your inserted values sequential but the gaps will still happen. Bottom line is that it shouldn't matter and if you are basing some sort of logic around them being sequential you need to re-evaluate that logic. – Sean Lange Jan 15 '15 at 21:14
  • @ZachSpencer, It matters it us because this id column is not only used as unique identifier but also as how many file are submitted/processed. We deal with millions of record everyday so we can't afford to run count command after every step to see the number of files processed/submitted. – Devang Jun 23 '17 at 03:01
  • @SeanLange, Thanks I have implemented something similar. Thought it takes a little more time but does the job. – Devang Jun 23 '17 at 03:04
  • Solution I implemented: I am reseeding the ID every time there is an error (in exception block). Reseeding is explained better in the other post mentioned above. FYI, `DBCC CHECKIDENT (mytable, RESEED, 0)` – Devang Jun 23 '17 at 03:04
  • I still say you are being overly concerned about your identity values. As explained already gaps are normal, consider a delete. But glad you figured out s solution. – Sean Lange Jun 23 '17 at 13:17
  • Yes, because we are using identity value to figure how many file are received and this value is used for generating report. This report goes to higher management so it has to be accurate. – Devang Jul 11 '17 at 15:19
  • I figured another solution to over come that. I could have used **COUNT** _function_ and got number of received files. But too late to update that now. – Devang Jul 11 '17 at 15:21

0 Answers0