February 17, 2006 at 12:46 am
hi, i have 3 records with the auto-incremented id(IDENTITY(1,1)
the current id's are 1,2,3
Now, i have deleted all these 3 records.
and inserted new records....but this time the id starts with 5 !!!
well, i want to reset the whole thing again ......so that id starts from 1
.
All the records i have deleted and from now onwards i want to insert records.
how do i reset the id ?
February 17, 2006 at 12:47 am
i am using MS SQL server 2000
February 17, 2006 at 1:23 am
dbcc checkident, check out the entire syntax in BOL and read the implications
HTH
February 17, 2006 at 1:53 am
Nothing understood.
i want to reset the id.
February 17, 2006 at 2:22 am
did you check the full syntax in Books Online?
This is a help file for sql2000,
otherwise go to msdn
February 17, 2006 at 2:38 am
thanks for the response.
here is whats i have found from MSDN
DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed, corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
Arguments
'table_name'
Is the name of the table for which to check the current identity value. Table names must conform to the rules for identifiers. For more information, see Using Identifiers. The table specified must contain an identity column.
NORESEED
Specifies that the current identity value should not be corrected.
RESEED
Specifies that the current identity value should be corrected.
new_reseed_value
Is the value to use in reseeding the identity column.
but, will it be helpful to reset the identity id to 1 and starts afresh ?
i am confused...because the manual does not say about resetting .
However, how do i uset it ?
i have a table , Employee ....it has a column "id" which is IDENTITY.
after deleting the table how do i resset the id ?
please explain.
February 17, 2006 at 2:41 am
pretty straightforward:
dbcc checkident('tablename',reseed,1)
if the table is still in development and you have no other table using that identity value for constraints or referential integrity, I guess it will ok to reseed it
only if that is what you mean...
February 17, 2006 at 6:54 am
What is wrong with gaps in your ID sequence?
Excluding other peoples views on ID columns (Joe Celko etc), the fact that gaps are causing you problems means you have design flaw - it should make no difference to anything else...
February 20, 2006 at 9:46 am
qbn is definitely correct - it shouldn't make a difference what the id value is. That being said, if you're refreshing the entire data set (e.g. remove all rows and then re-populate), you can issue a TRUNCATE TABLE tablename which automatically resets the id field to the value defined in the IDENTITY clause of the DDL for that table.
Mark
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply