AutoNumber reset

[NevG]

Hi troops

Does anybody know how when you have deleted items from a table like

TRUNCATE TABLE TableName

how you can reset the autonumber back to 0 or even if you can make the autonumber pick up from where you have deleted.

ie if you have 13 records and delete the last 3 the autonumber will pick up at at 14 insteaad of oging back to 11.

Any ideas how to fix this

Thanks

Nev

 

[Tim1]

Hi Nev,

The only way to reset the identity column (I assume that is what you mean) back to 0 is to drop and recreate the table. Otherwise, it will always resume from the next number in the sequence (possibly plus any gaps if you have not specified a table identity_gap = 1, or that is your server identity_gap).

Tim

 

[RickCole]

Ture you would have to drop and re-create the table in versions prior to SQL Server 7.0.

Check out BOL for DBCC CHECKIDENT as this can now be used to do what you require.

Rick.