Close id gaps

I have a given table
I want the id to be in sequence 1,2,3,4,5...
currently there are gaps 1,3,9,10....
there is no foriegnKey yet
and I canceled Identity increment.
what is the best way to do that?

drop .. recreate identity column ????

Not sure what you mean when you say you "cancelled identity increment". Did you recreate the table with no identity property?

If you are using identity column, the way to eliminate gaps would be what @harishgg1 suggested, namely, drop and recreate the column. While that would guarantee that there would be no gaps, the order may not be what you expect unless you take precautions. Search the web for how to assign identity values in your preferred order.

Also, starting in SQL 2012 (or thereabouts), it is not guaranteed that identity values will be sequential. That is to say, there can be gaps after you stop and start the server. See here . The workaround is to enable trace flag 272, but that is a server level setting, and you may or may not want to enable the flag.

Alternatively you can use a sequence object .

  1. Why do you want to do that?
  2. What is the reason there are gaps?
  3. What will you use to determine which row should be 1,2,3 etc?

is this a one time thing or do you want to keep this for historical purposes? Not sure how big the table is, but you could always use Row_Number, although it will be slow. something like this:

Create table #t 
	(id int,
	 word varchar(10))

insert into #t values
(1, 'test'),
(3, 'test5'),
(4, 'test6'),
(7, 'test9'),
(10, 'test111'),
(100, 'test222')

select Id, Word, 
		row_number() over (order by id) as rownum
from #t

It seems I wasn't clear enough, so here is the full story.
I store in the table words and their translations.
and there is also an ID which is a primary key and and it is also auto inserted (Identity Increment)
The ID is also the mp3 file name. (the files are stored in a folder)
I have already thousands of words in the table. (5906)
Because I deleted some records from the table there are gaps (eg. there are no records with id=5, or with id=6)
when I delete a record I delete his matching file too)
there is no problem so far.
I inserted an additional 1000 words to the table (i didn't have their matching sound files)
I deleted records... let's say about a 100 ( and again I have gaps)
so I have 900 words the first one's id is 5907, the last one is 6006
now I created their matching sound files (900)
their names are a sequence of numbers,
when I create the sequence I start from 5906.
If there where no gaps, everything is ok, and all id match the correct sound file (because I recorded them according a list I took from the table - order by id)
but because of the gaps i have a problem.
what I did, I removed the primary key and the identity increment from the id field (in my case it's posible this table is not connected to any other table)
I moved all the records after id=5906 to a new table (eWords3) and as an id i added the row_number
I added 5906 to each id
and inserted all those records to the original table (eWords2).

SELECT  row_number() over(order by id) as id,word,wordHeb
into eWords3
  FROM eWords2
  where id>5906
  order by id

delete  FROM eWords2 where id>5906

update eWords3 set id=id+5906

insert into eWords2  select * from eWords3

it works but it's very complicated.
It's not working!

harishgg1, JamesK
your solution is not good for me as I want to apply it to part of the table (starting where id>5907)

I want to keep the original order (order by id) because according to this order I created the sound files

this query doesn't update the id column
and how I apply it to let's say start from id 7


imo I think this is more of a design issue than a SQL issue and is not sustainable. you probably should look for another column to try and associate with your files. Your design is tightly coupled with an ephemeral column identity column which you go right around and delete, impossible to maintain because tomorrow you will delete more rows and then it will be a nightmare to maintain this. you are using an identity column for something it was not intended to be used . PK should mainly be used to create relationships between tables.

1 Like

It's not going to happen again.
I insert new records (they are sequence)
I create their files (also a matching sequence)
and if after word i delete records there is no problem.
The problem happened because I deleted records and afterward I created the sequence of files.
i thought there is a simple solution somthing like
update eWords2 set id = (select max(id) as maxId from eWords2 where id>maxId )
but if there is not I will think it over.

hi elic

please dont mind my saying this
i am having a tough time understanding what your issue is ..
if i understand than I can think and come up with a solution


is it possible to do this by screenshots
giving simple examples
like this

drop create data ...
use tempdb 

drop table A 
create table A 
id int identity(1,1),
name varchar(100)

insert into A select 'har'
insert into A select 'pra'
insert into A select 'rav'
insert into A select 'dadu'

select * from A


delete from A where id = 3

select * from A

here i deleted 3... i want to insert from 3

hi elic

i think this is what you are trying to say

you first created words like this
deleted word "please"

then created sequence

Here 3 shoud be for deleted word "please"
but 3 is now for word "fine nice"

am i correct in understanding ????

I created sequence, but not in the table. I have a folder with sound files, those files have names, and their name is a sequence 1.mp3, 2.mp3, 3.mp3 etc...
I must create them like that, because I split a long file to pieces and each file gets is name automatically.

now if I want to match the ids with the file names I have a problem.
the ids is not a sequence but the file name is a sequence.

ok elic

why not make the id's in sequence first then match with the filename

below is just an example of text files in folder with names .. just like your mp3 files

Usually that's what I do.
But this time I added records and, before I created the sound files I deleted some of them
Now when I create the sound files there won't be a match unless I close the gaps in the id's


please take a look at this article

hope it helps

1 Like

I would recommend

  1. come up with a better image file naming convention instead of 1,2,4 etc ?
  2. wipe your database clean (in development) then use powershell to iterate through the folder and recreate the rows in the database.

If an ID field has meaning other than just an ID for a particular record then you are using ID's WRONG. An ID field should NEVER have meaning other than identifying a row of data. You should redesign so that the ID does not have meaning.