reset identity numbering? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

reset identity numbering?

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2005-11-07 : 14:48:22

Any ideas on how to re-set a primary key's identity number to count from number 1 again?

I made a new entry in my table which had a ProductID of 1. I then deleted the row and replaced it with new information. But now the first row is returning a ProductID of 2.

How would I get it back to number 1?

Cheers,
Token

Kristen
Test

22859 Posts

Posted - 2005-11-07 : 14:53:35

[code]
DBCC CHECKIDENT ('dbo.MyTable', RESEED, 1)
[/code]
Kristen

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-11-07 : 14:54:00

DBCC CHECKIDENT ('tablename', RESEED,1)

or use truncate table

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-07 : 16:21:09

You shouldn't need to worry about that...supplying an artificial meaning to an identity column is just dangerous.....are you always going to be concerned about gaps?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

token
Posting Yak Master

133 Posts

Posted - 2005-11-07 : 18:58:57

I know what you mean. The 'perfection' is all in my mind. I needed to reset the numbering for testing purposes. I am developing an e-commerce store and I am using a default URL parameter of '1' to test connectivity. And yes, I HATE GAPS!

quote:
Originally posted by X002548

You shouldn't need to worry about that...supplying an artificial meaning to an identity column is just dangerous.....are you always going to be concerned about gaps?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-08 : 00:41:40

Other option is to do it from EnterPrise Manager
If you want to delete all the records and want to reset identity value, then you can use Truncate than Delete

>>I HATE GAPS

why?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-11-08 : 01:11:48

"And yes, I HATE GAPS!"

Oh dear! This is going to create one:


CREATE TABLE PRODUCTS
(
	ProductID INT identity(1,1) NOT NULL,
	ProductName varchar(30) NOT NULL,
	PRIMARY KEY
	(
		ProductID
	)
)
GO
INSERT INTO PRODUCTS(ProductName) VALUES ('FOO')
GO
BEGIN TRANSACTION
INSERT INTO PRODUCTS(ProductName) VALUES ('BAR')
ROLLBACK
GO
INSERT INTO PRODUCTS(ProductName) VALUES ('FOOBAR')
GO
SELECT * FROM PRODUCTS ORDER BY ProductID
GO
DROP TABLE PRODUCTS
GO

ProductID   ProductName                    
----------- ------------------------------ 
1           FOO
3           FOOBAR

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-08 : 01:19:26

Well. Rollback wont rollback generated id. In this case I prefer having Primary key either enterable by user or generated based on Max Product Id

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources