identity column

I have a identity column in table A ( increment by 1).. after i truncate the records in the table, i would like to start the indentity nummber from 1 everytime.. not strting from the next number of last records that got deleted...

for example, if there are 1000 records in the table.. i would like to start the identiy number start from 1 again after deleting the table... not starting from 1001... how can i do this?
jung1975Asked:
Who is Participating?
 
frankyteeConnect With a Mentor Commented:
i would have thought that truncating a table would reset the counter back to 1. if it does not then drop and recreate that table.
0
 
SQL_SERVER_DBAConnect With a Mentor Commented:
DELETE TABLE
SELECT * INTO <deleted_table_name>
0
 
frankyteeConnect With a Mentor Commented:
the below syntax
truncate table mytable
should reset the identity
if it doesn't then try
DBCC CHECKIDENT (table_name, RESEED, new_reseed_value)
where new_reseed_value = 1 for your situation
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jung1975Author Commented:
there is a foreign key relationship...so i cannot truncate table..
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
summary from above suggestions:

--- delete all rows from the table
DELETE YOURTABLE
--- reset the identity to the next available value:
DBCC CHECKIDENT (YOURTABLE , RESEED )
0
 
matrix_aashConnect With a Mentor Commented:
CREATE TABLE [dbo].[YOURTABLENAME](
      [IDENTITYCOLUMN] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
      [autokey] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 Please try this after you have dropped the table.

Cheers

Aash.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.