Auto Primary Key with fill in

I want a table to have an int auto primary key, that will fill in deleted rows.
EG
1
2
4
5
8

The next primary key should be 3 (NOT 9)

This table changes often, so just adding 1 to the last number used, will fast run out of numbers.
The table is accessed by many users, so it can not be manually generated.

Using the IDENTITY property will successfully and correctly manage autonumbering. However, as you noted, deletions will leave gaps. I suggest that you use a datatype sufficient to allow both growth and allow for the deletions. A bigint datatype is a very, very large number -up to 9,223,372,036,854,775,807, I couldn't imagine that you are adding so much data to the database that a bigint would be inadequate.

It would be possible to create a 'homegrown' solution to manage this, but in my experience, with many users, that is a mistake. It would require a lot of table locking and/or data contention, placing unneeded stress on the database.

There are occassionally, real business needs for managing a numbering sequence, for example check numbers. But in those cases, deletions are usually not allowed.

|||Yes and after about 1 year or so when it flips over to 1 again, and starts over writing data.

Its a very nasty bug.

Its happened before.|||

Hi:

Please refer to the following article, and see if it would help with your concern. :-)

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

Thanks.

|||That changes existing data.

but can be expanded upon, to use a multi sql statement to defrag a database.

but then how to reset the auto number to the next number.

So far the only way, i can do what i want, is one of two ways

1. Run all sql through a service, that can sync me a new auto fill in number.
2. Have unacceptable down time, and run a program to defrag the tables.

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me...
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>