auto increment

I have a table that has teh primary key set so it auto increments.
How can I make it to where if I delete a record, the next entry will fill
the spot that was vacated?
For example;
I have 5 records,
1
2
3
4
5
If I delete record 3 I will have
1
2
4
5
Then if I add another reocrd, I will have
1
2
4
5
6
I would like 6 to actually be 3 to fill in the space.
or, when I delete a record, have everythign shift locations, or at least
have teh last record fill the spot of the deleted one.Number the rows when you SELECT from the table. Here's an example from the
Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS id
,*
FROM Authors AS A
David Portas
SQL Server MVP
--|||I agree with Dave, you don't need to STORE this number, just return it when
you run your SELECT (http://www.aspfaq.com/2427). You can do this
quasi-transparently by using a view instead of a query, but the effect is
the same.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||Why. SQL doesn't charge by the each for auto-increment row numbers. If you
use BigInt, please call me if you use up all your numbers. I will be happy
to refill the mfree of charge.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||What I am doing is I have a database of the drivers for our company.
We have to do random drug tests.
I wrote a program that randomly pick numbers from 1 to the number of records
in the database.
But if a driver gets the axe, I delete him, then add another, the program
will periodicly pick the number for the driver that was deleted. I have
already changed my program to deal with that by if I get a BOF or an EOF, to
go pick another number. But the problem is, is that I may have five records
1,2,4,5,6 It will pick a number from 1 to 5, but it will never get number
6.
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:enKwjs17DHA.632@.TK2MSFTNGP12.phx.gbl...
> Why. SQL doesn't charge by the each for auto-increment row numbers. If
you
> use BigInt, please call me if you use up all your numbers. I will be happy
> to refill the mfree of charge.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "johnfli" <john@.here.com> wrote in message
> news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
fill
>|||Try picking a random driver like this
SELECT TOP 1 driver_name
FROM Drivers
ORDER BY NEWID()
David Portas
SQL Server MVP
--