Re: Reset Identity Column

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 04/22/04 

Date: Thu, 22 Apr 2004 11:23:22 -0400

Either truncate the table ( it will automatically be reset)
or
DBCC checkident with a reseed parameter ( this is doc'd in books online.. 

--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chuck" <anonymous@discussions.microsoft.com> wrote in message
news:2bd401c4287c$8a1ba5e0$a601280a@phx.gbl...
> Hello,
>
> I have a database table that I will delete all the
> contents from regularly.  I have an identity column that
> I would like to start counting from 1 after deleting all
> the records.  However it keeps counting from the last
> number it used even though all the records are deleted.
> Is there anyway to reset the Identity number.
>
> Thanks!

Relevant Pages

  • Re: Identity column overflow
    ... >BG, SQL Server MVP ... >> I recently discovered that when an identity column ... method to handle a reset ... >numbers which may be foreign keys? ...
    (microsoft.public.sqlserver.programming)
  • Reset Identity Column
    ... I have a database table that I will delete all the ... I have an identity column that ... I would like to start counting from 1 after deleting all ... Is there anyway to reset the Identity number. ...
    (microsoft.public.sqlserver.server)
  • Re: AFTER INSERT?
    ... If you have an identity column you can really reset the value from within a ... is there a After Insert trigger functionality in SQL Server 2000? ... > and reset it if it has passed a certain limit. ... > It appears that I cannot do this WHILE I am inserting, ...
    (microsoft.public.sqlserver.programming)
  • Re: Lost SA Password
    ... There is one other way to get reset your sa password to blank and reset the ... Copy the master data file and log file to another SQL Server. ... Attach the database as mastersa or some thing like that. ... >>Also don't have sa login, ...
    (microsoft.public.sqlserver.security)
  • Re: Ranking Usiing Autonumber
    ... It should reset if you compact the database. ... > data for perfect scores, and then appends again for those ... > I was hoping to us auto number to increment by 1 for every ... > continues counting from where it left off. ...
    (microsoft.public.access.tablesdbdesign)