Reset the Identity Increment

Reset the Identity Increment

Hello:
I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the
table.

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the
table.

The DB is MS SQL Server 2000.

Thanks and appreciate any help.

Ryan KennedyCheck out the DBCC CHECKIDENT command in google^h^h^h^h^h^h Books Online|||Also look at the TRUNCATE statement.

"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:53dPb.1926$kH2.252@.nwrdny01.gnilink.net...
> Reset the Identity Increment

Reset Primary ID back to 1

Reset the Identity Increment
------------------------

Reset the Identity Increment

Hello:
I have a table with a bigint type column (field) that has an identity seed
of 1 and an identity increment of 1. The column is the primary key for the
table.

After I backup and clean out the database (delete all of the data in the DB)
I need to have the column with the identiy seed/increment value reset to 1
automatically. (start counting at 1 again). How does one do that, because
as it is now, the DB keeps increasing the value of the column from where it
left off, regardless of the fact that I deleted all of the data in the
table.

The DB is MS SQL Server 2000.

Thanks and appreciate any help.The only way to do that is to use truncate table instead of delete.
You need additional rights to be able to execute truncate table statement.

Good Luck.

Irina.|||Yes, of course you can use truncate. It will delete and reseed the identity columns.
It is also more efficient way to save the resources.

But, if you also want to use delete, you can reset the identity column by running the following command:

DBCC CHECKIDENT('mytable', RESEED, 0) ;

Hope to help.

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

Reset Increment Seed

I'm still in the development stage and am frequently deleting all data from all tables and then filling those tables anew. However, the increment seed for identifying fields doesn't reset to 1 (or 0--not sure which). While not important for operation of the database, I would prefer that the field identifiers start with 1 when I am ready to release the database for operation. Is there a way to do this?

I can generate scripts to rebuild the database structure to do this, but scripts aren't generated for database diagrams and the graphical representation of the table structure is very useful.

Hi Joe,

Truncate table reset the indentity. Truncate table is faster and need no transaction logs.

regards

Laurent

|||

You should take a look at DBCC CHECKINDENT in Books Online:

eg DBCC CHECKIDENT(YourTable, RESEED, 0)

HTH!

|||

Exception is that TRUNCATE TABLE will not work for table with reference to foreign keys. Almost all the table have references to foreign keys. Even when none of the tables have data in them, you still can not truncate the table.

Will try

DBCC CHECKIDENT(myTableName,RESEED,0) WITH NO_INFOMSGS

sql

RESEEDING & INCREMENT VALUES

hi,
I am maintaining around 180 tables, out of these-125 have identity values
I need set new SEED value and new increment values for these 125 tables
Is there any easier way to do this?
Thanks,
Soura
you can do something like this
SELECT 'DBCC CHECKIDENT ('+TABLE_NAME +',RESEED , 50 )'
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_INCR(TABLE_NAME) IS NOT NULL
This will reseed the next identity value to 50.
I'm not sure how to change the increment.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:352CE298-4960-43AB-8C9B-A098FF4CAB6F@.microsoft.com...
> hi,
> I am maintaining around 180 tables, out of these-125 have identity
values
> I need set new SEED value and new increment values for these 125 tables
> Is there any easier way to do this?
> Thanks,
> Soura

Reseed The Identity Column

Hello there,
I am having problem in modifying the identity column of a table.

Currently the identity column is seeded by 1 with an increment of 1 and DATA with these properties of the column is stored.

Now, I want to reseed the identity column with an increment of 2.

The query I am using is

ALTER TABLE XP ALTER COLUMN XP_ID BIGINT IDENTITY ( 500, 2 ) NOT NULL

Is there any one out there who can help me?Take a look at DBCC CHECKIDENT() in sql book online.|||I have to ask why you want an increment of 2. There are a lot of things that CAN be done in SQL Server that probably SHOULDN'T be done in SQL Server...|||Take a look at DBCC CHECKIDENT() in sql book online.

Thank you very much.

You have told me a nice thing but I reached there before you told me.

Thanks for the solution.|||Some government must want to put a tax on odd numbers, is all I can think of. The price of going international, I suppose ;-).|||I need to populate a temporary holding table from a trigger. The contents of this table will be added to another table when the Trigger has completed processing. The id column of the temporary table will be added to the max value in the final table to create the record identifier. I used an identity field in the temporary table to accomplish this. My initial idea was to create a temp table in the code for the trigger then drop the temp table when each recursion is complete. However I got "cannot use Create table in Trigger". Then I tried to make temp table in stored proc called by trigger. Same message. Then made permanent "temp" table, and tried to Trucate table after each use. I got "Cannot use Trucate Table in Trigger". Finally I used delete from and DBCC Checkident to reseed identity. This worked in Query analyzer. I worked in VB project using ADO. However, I after deployment I was informed that VB project using RDS to make and update ADO recordsets was failing to run the trigger. After troubleshooting I was alerted that DBCC CheckIdent can only be run by table owner. Business rules prevent this. Therefore I would like to know if anyone knows any other way to reseed an identity, before I abandon this approach and start over.

Thanks for trying.sql