Auto increment

[grimmy]

I have written a VB App that writes to a SQL 6.5 DB and I am having a small problem with on table.
Within this table I have a column that I have setup to auto increment by 1 each time a record is added and I show this column in my VB app as record number.
The problem is that whilst testing that this works I have been adding and deleting records and now the table is empty but the count for the autonumber is not reset to zero and when the first record is entered it says that it is record 10 for example.
How do you reset this figure back to Zero.

Cheers.....Grimmy

 

[bperry]

If the table is already empty (or you don;t care about losing any records, here's a quick way:

TRUNCATE TABLE tablename

This will reset the IDENTITY to the seed value (usually 1).

rgrds, etc
brian perry

 

[jhall156]

I have seen this question in various forms quite a few times. A short answer would be if the table is empty, recreate it with the increment seed reset to zero. But I have to ask, why do you need this? If you are going to use this field as a surrogate key then it shouldn't matter what its value is, if you want to do that AND use the value as a record counter I think you'll find most of the people in here will tell you that this is not the best practice. JHall

 

[bperry]

By the way, BooksOnLine mentions another way to reset the Identity value:

DBCC CHECKIDENT('tablename',RESEED,0)

In this case, 0 represents 1 less than the next assigned value. i.e. if you want the first new Identity value to be 1, then enter 0 above. (I think) You should check it out in BOL before trying it, though.

>>The problem is that whilst testing that this works I have be....
What you are wanting to do is not usually recommended in live systems, but I have often done it in test situations, like you seem to be doing.

rgrds, etc
brian perry