Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scmay
Starting Member
22 Posts |
 Posted - 2007-06-05 : 22:19:58
|
| Hi, I am using SQL Server 2000 and the clients would like to archive and remove some of the old records from certain tables in the database. I would like to know the methods of setting ID as autogenerated and if the ID will be initialized to 0 again if records are removed, and if autogenerated IDs reuse missing IDs (e.g. I have ID 1-10, remove ID 2, will the next new record be ID 2?)We need the ID to keep incrementing and not reuse missing IDs, nor reinitialized once we've archive the old records, and I was thinking of storing all the IDs in a table. Just asking for the possibility of SQL Server autogenerated ID does this. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-05 : 22:42:47
|
| You can set id as identity column, and can reset the value with 'dbcc checkident'. |
 |
|
|
scmay
Starting Member
22 Posts |
Posted - 2007-06-05 : 23:49:20
|
quote:
Originally posted by rmiao
You can set id as identity column, and can reset the value with 'dbcc checkident'.
How do I set a column as identity column from Enterprise manager? |
|
|
|
pbguy
Constraint Violating Yak Guru
319 Posts |
Posted - 2007-06-06 : 00:43:12
|
| 1. Select a table2. Right click3. design table from pop up4. for each column,the column property will be displayed at the bottom5. There is identity , Identity seed, Identity increment are available (to set seed you should make identity to 'yes')--------------------------------------------------S.Ahamed |
|
|
|
scmay
Starting Member
22 Posts |
Posted - 2007-06-06 : 02:30:00
|
thanks! quote:
Originally posted by pbguy
1. Select a table2. Right click3. design table from pop up4. for each column,the column property will be displayed at the bottom5. There is identity , Identity seed, Identity increment are available (to set seed you should make identity to 'yes')--------------------------------------------------S.Ahamed
 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-06 : 03:09:45
|
| "We need the ID to keep incrementing and not reuse missing IDs, nor reinitialized once we've archive the old records"IDENTITY does exactly that. It will NOT reuse numbers from gaps, and it will NOT reset if you delete all the records - if you want to reset it you have to explicitly use DBCC CHECKIDENT, as rmiao said.Kristen |
|
|
|
|
|
|
|
|
Handling autogenerated ID