• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Reset Identity Incremental No in SQL Server Table

I have a the table which consists of the auto incremental no when each of the new records inserted.

When i delete all of the records from the table and insert a new record, the incremental no will still remain continue. So how do i reset the incremental no to 1 ?
0
dynamicrevolutions
Asked:
dynamicrevolutions
1 Solution
 
Saqib KhanSenior DeveloperCommented:
Change the Value of IDENTITY Column to "NO" in EP then Save, Then Switch it back to "YES" and Save the Table.
Thats it:)
0
 
arbertCommented:
Or use DBCC CHECKIDENT
From books online:

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.

Remarks
If necessary, DBCC CHECKIDENT corrects the current identity value for a column. The current identity value is not corrected, however, if the identity column was created with the NOT FOR REPLICATION clause (in either the CREATE TABLE or ALTER TABLE statement).

Invalid identity information can cause error message 2627 when a primary key or unique key constraint exists on the identity column.

The specific corrections made to the current identity value depend on the parameter specifications.

0
 
imrancsCommented:
short to arbert's suggestion

DBCC CHECKIDENT ('MyTable', RESEED, 0)

now it will restart identity from 1.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now