Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reset Identity Incremental No in SQL Server Table

Posted on 2004-10-28
3
Medium Priority
?
308 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
Comment
Question by:dynamicrevolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

Accepted Solution

by:
Saqib Khan earned 2000 total points
ID: 12440883
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
ID: 12440957
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
ID: 12441282
short to arbert's suggestion

DBCC CHECKIDENT ('MyTable', RESEED, 0)

now it will restart identity from 1.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

597 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