-2

I used this code to create a table which would increment ID by every record:

CREATE TABLE new_employees
(
 ID int IDENTITY(1,1),
 NAME varchar (20),
 BALANCE int
)

But there is a problem , let's say i want to delete id 6 and after add another record but the new record doesn't take that id 6 but it goes to the next id (7) how can i make it so that if I delete a record all the records after it get a decrement in the ID?

Andrew V
  • 522
  • 10
  • 24
  • What's the benefit? I think in the long run this would just make things more difficult. – Elias Feb 19 '14 at 19:32
  • Don't rely on identity to keep an incremented value. Normally it will be, but you can easily run into cases where there'll be gabs, even without deleted rows. You should maintain the value yourself. But, I agree with @Elias, what's the benefit? You can always select a row number if you need one without it being persisted. – Allan S. Hansen Feb 19 '14 at 19:32
  • 5
    You should **NOT** try to do this!! It's a horribly bad idea to "recycle" values that have already been assigned to a row previously! **Don't do this!** – marc_s Feb 19 '14 at 19:32
  • Can you tell us _why_ you'd like to do this? What are you looking to accomplish by simply having no gaps due to deletes or rolled-back transactions (the other common cause of identity gaps)? – Tim Lehner Feb 19 '14 at 19:44
  • im doing an app in java which is connected to sql so i can't really select a row... – Andrew V Feb 19 '14 at 19:45
  • well i just wanted to know if i can do this and if it's correct – Andrew V Feb 19 '14 at 19:46
  • Please rethink what do you need. If you are erasing the table just use truncate. By trying to reseed the identity is a real bad idea. – jean Feb 19 '14 at 20:07

3 Answers3

1

It's bad practise, but it's possible still.

You could do it like this:

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

Also have a look at this thread: Reset AutoIncrement in SQL Server after Delete

Community
  • 1
  • 1
Fabian Bigler
  • 10,403
  • 6
  • 47
  • 70
  • You probably should use something like `select @max = coalesce(max(key), 1) from table;` to avoid issues if the last record was deleted and the table is empty. – FrankPl Feb 19 '14 at 19:51
  • @Fabian Bigler What if you have ID's 1-10 and you delete ID 8. So, now you have 1-7 & 9-10. What happens with this? I am thinking that MAX would still be 10 so the next ID would still be 11. – Guy Nethery Feb 19 '14 at 19:54
  • Yes, but IMHO the question is not clear at this point. " i want to delete id 6 and after add another record but the new record doesn't take that id 6 but it goes to the next id" => so I assume he wants to get the NEXT available ID. – Fabian Bigler Feb 19 '14 at 19:56
  • You dont need to get the max value and reseed it. if you just execute the DBCC command without any seed value it will reset the Identity value to next available highest value. – M.Ali Feb 19 '14 at 20:01
  • You answer is correct but I can consider downvoting you just to give OP the answer he can use to do a horrible thing. Also he can just truncate the table. That ill reset the identity. – jean Feb 19 '14 at 20:05
  • @jean I'm here to answer questions, not to debate if it's the best way to do. There have been worse questions like 'how to parse HTML with regex' or heaps of code which is prone to sql injections. and by the way, my first sentence is: "It's bad practise, but it's possible still." to point out its bad practise – Fabian Bigler Feb 19 '14 at 20:17
0

You cannot. That is not how identity columns work. In fact, the values can have gaps even without deletes.

Instead, do the calculation when you query:

select row_number() over (partition by id) as seqnum

You can maintain such a column if you really want using triggers or similar logic on updates. Identity does not work this way, because gaps can be caused for other reasons.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • In fact, he can use dbcc checkident to reseed the id. But it depends on how you understand his question – Fabian Bigler Feb 19 '14 at 19:42
  • down voted. It's a horrible idea to do it but it's possible to reseed. Also truncating the table ill reset the identity – jean Feb 19 '14 at 20:04
  • @jean .. Even reseeding the identity doesn't fix the problem of gaps caused by other reasons. Reseeding does not guarantee no gaps. And, you still have to update all the intermediate ids. Yes, you can do it with a trigger, but then it's not really an identity column. – Gordon Linoff Feb 19 '14 at 20:16
0

Not the best thing to do. If you have an Identity field then just let it generate the values for you. If you want to be able to pass values your self then just dont make it an identity value at all.

But if do want to do what you are trying to do then you could do something like this...

CREATE TABLE new_employees
(
 ID int IDENTITY(1,1),
 NAME varchar (20),
 BALANCE int
)
GO
INSERT INTO new_employees (NAME, BALANCE)
VALUES ('Name 1', 1),('Name 2', 2),('Name 3', 3),
('Name 4', 4),('Name 5', 5)
GO

DELETE FROM new_employees
WHERE ID > 3

DBCC CHECKIDENT ( 'new_employees', RESEED,0 )  -<-- with seed value 0

Checking identity information: current identity value '5', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKIDENT ( 'new_employees', RESEED)   --<-- WIth out any seed value

this will reseed the value to highest nest possible Indentity value.

Checking identity information: current identity value '0', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

INSERT INTO new_employees (NAME, BALANCE)
VALUES ('Name 4', 4),('Name 5', 5),('Name 6', 6)
M.Ali
  • 67,945
  • 13
  • 101
  • 127