Help - deleting existing auto-generated primary key

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Another common practice is to create some sort of an ID column as well as the auto-generated primary key. Sounds like this way work better for you in this case if you plan on frequently deleting, inserting, etc.

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?