Solved

Reset Identity Incremental No in SQL Server Table

Posted on 2004-10-28
307 Views
Last Modified: 2008-01-09
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
Question by:dynamicrevolutions
    3 Comments
     
    LVL 23

    Accepted Solution

    by:
    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
     
    LVL 34

    Expert Comment

    by:arbert
    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
     
    LVL 10

    Expert Comment

    by:imrancs
    short to arbert's suggestion

    DBCC CHECKIDENT ('MyTable', RESEED, 0)

    now it will restart identity from 1.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    By Mark Wills We often hear about Fragmentation, and generally have an idea that it is about broken bits, or bad for performance, or at least, is generally not a good thing. But what does it really mean ? By way of analogy, think of the humbl…
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now