You need to set up a column with the IDENTITY property (
link to documentation[
^]
The number will automatically be incremented everytime you add a row. If you delete a row the id number is not reused, so the number can be deemed to always be unique within this table.
Be very cautious of attempting to do something similar yourself manually (e.g. using MAX(id) to assign the "next" number) as any solution may not be robust in a multi-user environment
[EDIT - OP has made it clear that the id numbers must be consecutive after a deletion]
With a nod to solution 2 as this is very similar ...
First I will create a sample table put some data in it and query it to show how the ID has been generated for us
CREATE TABLE dbo.EXAMPLE
(
[ID] [int] IDENTITY(1,1),
[FLD1] [varchar](20),
[FLD2] [varchar](20)
)
insert into EXAMPLE VALUES('x','A')
insert into EXAMPLE VALUES('Y', 'B')
insert into EXAMPLE VALUES('z', 'C')
SELECT * FROM EXAMPLE
produces the output
ID FLD1 FLD2
1 x A
2 Y B
3 z C
Now that we have the data set up I created a stored procedure to reserialise this table (note I haven't tried to make this generic but it is possible, if long-winded, to do so)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ReserialiseEXAMPLE]
AS
BEGIN
SET NOCOUNT ON;
SELECT FLD1, FLD2 INTO #RESERIAL FROM EXAMPLE
DROP TABLE EXAMPLE
SELECT IDENTITY(int, 1,1) as ID, * INTO EXAMPLE FROM #RESERIAL ORDER BY ID
DROP TABLE #RESERIAL
END
GO
Now delete a record and add a new one and see what's in there...
DELETE FROM EXAMPLE WHERE FLD2 = 'B'
insert into EXAMPLE VALUES('a', 'D')
SELECT * FROM EXAMPLE
and we get
ID FLD1 FLD2
1 x A
4 a D
3 z C
But now call the stored procedure and compare the results
dbo.CHill60Reserialise
SELECT * FROM EXAMPLE
gives us
ID FLD1 FLD2
1 x A
2 a D
3 z C
Couple of things to point out ... this is an incredibly inefficient way of dealing with this so I would advise finding a way of clumping together all of your deletions (e.g. mark the records to be purged) and run the SP as little as possible.
Also (as per comments in Solution 2) this was affected by doing an insert before the reserialise and can also be affected by order by.
Lastly - you specifically stated SQL2000 in your post ... be aware that from SQL2005 onwards there are better ways of generating ROW_NUMBER or RANK - which is essentially what you're trying to do here