Auto Increment Always adds 1 to previous Row, Even when previous row has been dropped

Question

Possible Duplicate:
Reset AutoIncrement in SqlServer after Delete

I'm having an annoying issue with an Identity auto-increment column value within an Sql Server 2008 database. When I delete a row from my table (or delete all rows), when I add a new entry it's incremented from whichever value was inserted into a previous record, regardless as to whether that record still exists.

I was wondering if anyone knows how to get it so that the IDENTITY column will auto-increment from whatever the previous record that EXISTS is.

My code looks like this:

    CREATE TABLE Product
    (
            Product_Barcode INT NOT NULL PRIMARY KEY IDENTITY,
            Product_Description VARCHAR(255),
            Product_Name VARCHAR(255),
            Product_Supplier VARCHAR(255) FOREIGN KEY REFERENCES Supplier(Supplier_Name),
            Product_Size VARCHAR(255),
            Product_Weight FLOAT
    );

Answer 1

Typically you wouldn't want this because new and past records can share IDs. If you have referential constraints, this might cause confusion.

The only way I know how to do it though, is by inserting using a subquery eg.

INSERT INTO Product (Product_Barcode, Product_Description) VALUES (SELECT MAX(Product_Barcode) + 1 FROM Product, "Any description here");