How to set the database to always use next increment number?

Is there a way to make the primary ID as Identity Column to always be in order? For example, I have 5 rows with ID 1,2,3,4,5. If I delete record number 5 and then added another record, the ID shows up as 6 and not 5. Or if I delete record number 3, the next ID is going to be 6 instead of 3. I like to keep all my ID in order if possible and not skipping if that is even possible or should I use that practice.

I think its good way if ID is incrementing by this you can come to know which record inserted when and so on ....

A good article on identity

http://msdn2.microsoft.com/en-us/library/ms971502.aspx

|||

it is possible but you have to do it yourself by switching identity insert ON and OFF in your code and taking care about inserted value. It is not recommended way because it slow down insert process and also it can destroy your related record information if you have any, If you would like to have records order information for some purposes you can always do select statement like:

select identity(int,1,1) new_ID, old_id
into #test
from yourtablename
select * from #test
order by new_ID

and next select by new_ID your result.
You can also use ROW_NUMBER:

select ROW_NUMBER() OVER(ORDER BY old_id ASC) [OrderNo] from from yourtablename

|||

Many thanks for all your responses. The reason I was thinking about doing that is because this table is for linking pages. These links will be added and deleted often..and if there is no way to keep the ID in order, I'm afraid the ID may get too big but with less than 40 or 50 actual records in the table.

|||

There is no inbuilt solution for your problem.. you need to use alternative methods..

2 of those methods are discusses here.

http://www.sqlteam.com/item.asp?ItemID=765

|||

Many thanks for the link. It looks like I could use this:

declare @.intCounter int
set @.intCounter = 0
update Yaks
SET @.intCounter = YakSequenceNumber = @.intCounter + 1

The question I have is, do I run the above code everytime when I want to update and insert? Or do I run this code at the begingin where there is no record yet in the table?

|||

I think you need to use this while you are inserting. Is this make sense

|||

Okay, so can you give me a sample code of how I would use this while inserting in C# code behind? I'm still unclear as to how I would use it. By the way, if I'm using this, do I turn of the column Identity?

|||

I will create one example for you can give it to you

|||

This one solution has one big disadvantage that it will slow down your insert so maybe you should run this one time a day(or hour) for your table instead of running it on every insert?

But solution is very nice