hi all,

i have an auto-increment field and need to reset it each month so it will not get a big value
example:
in the end of february the id was 34576 on the start of march i need to reset this field automatically to start with 1 any idea how to do it?

Yes, but tell me, what do you want to do with the numbers in that column that already exist? You cannot simply reset the numbers to 1 if 1 already exists in that column? Now, if this is just for display purposes, there's better ways to do it and it won't break any Declarative Referential Integrity (DRI) that you may have.

__________________
--Jeff Moden

If the column does not have a unique constraint and is not a primary key, this is possible, but as Jeff is getting at, it is not necessarily the best method to use.

There are many ways to do this, or achieve the same effect, but which way you choose depends on:
- will you keep existing data from previous months?
- similarly, is this a primary key column?
- what type of front-end or processes are you using to manipulate data (web site, automated services)? It may be more appropriate to work out the value to use on the front end.

For your information tho, you can reset an identity seed by truncating the table (TRUNCATE TABLE MyTable;) or by using a DBCC RESEED command:

thanks guys for the quick reply,
i need to keep the old data, i am using a purchased software. i added a field in the database and i need this field in a report
it is not a problem to remove the primary key

Actually, it IS a problem if you remove the primary key for many reasons. Read about what a "heap table" is and what some of the disadvantages are including the possible reduction in performance.

What would probably help us help you, at this point, is the CREATE statement for the table and any indexes and triggers that may also be on the table. Provided there isn't any private or sensitive information in them, a sampling of rows contained in the table would help, as well.

__________________
--Jeff Moden