I have a table Ex: Students
 with Primary key is StudentID
it automatical increase values when I insert new one

but When I delete One . The value of StudentID will be hole

and Now I want to Compact all the Hole values when I delete

How to to this ?

thanks you in advanced !

wht do u mean by "The value of StudentID will be hole"

and "I want to Compact all the Hole values"

ex:
StudentID LastName
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J


When I Execute query "Delete from student where studentid=4"

then My Table will be

StudentID LastName
1 A
2 B
3 C

5 E
6 F
7 G
8 H
9 I
10 J

The value 4 will be hole

and now I want my Table become

StudentID LastName
1 A
2 B
3 C

4 E
5 F
6 G
7 H
8 I
9 J

If you don't mind me asking: Why would you want to do something like that? It does not make any sense. What about the potential links from other tables to the studentid? The studentid would become meaningless...

Gert

Yes ! i see

but now i want my table like that . Can you help me

if you use an identity value, you have to reset the identify value after you reshift everything. My suggestion is select the data into another table of the same structure. Let the new table reassign new key values, the drop the old table, recreated the old table in the proper name, then select all the data back once again so you can preserve the table name. But I agree with the person above why would you want to do that? Perhaps a better idea would to be to not use an identity value?

Yes !

you want to know what's my goal .I would like my table like this because on my site i want to create a web site pagging able . but i dont like creat #temp table and then insert data into

because it take a long time to execute.

Can't you just write a "paging" stored procedure to retrieve the data but leave the data the way it is in the database with the gaps? I think this would be much quicker. Anytime you change the key value of your table to remove the duplicates you also have to change all the values of the other tables foreign keyed to this table that your changing. This creates A cascading change nightmare.