hi every body
i need to reorder the auto number column in sql server 2000
i.e
let we have the column called "Id" and this column is auto numbering
(aka identity)and we have the following values :
1,2,3,4,5,6 if we delete the record which have id=4 the values will be:
1,2,3,5,6 (see the order of number )
i need to reorder this values to be :
1,2,3,4,5

any one help me plz?!!
thank you

Well, as long as this column in not being used as a FK (foreign key) anywhere, you can manually delete the column and re-add it using the same properties it has now (autonumber, etc). You can also do this via T-SQL as well.

the problem here is this request must be done for a many table some of this table the column id is fk in other tables
but , any way thank you very much and i wish if you can write the t-sql statment which doing that
and thank you agian

--First, drop the pk constraint on the original Id column
ALTER TABLE tablename
DROP CONSTRAINT constraintname
GO

ALTER TABLE tablename
DROP COLUMN Id
GO

ALTER TABLE tablename
ADD Id int IDENTITY CONSTRAINT PK_tablename_Id PRIMARY KEY CLUSTERED
GO

Again, i would not recommend this on tables where the PK is being used as a FK to other tables. You are just asking for trouble.