Reset IDENTITY(1,1)  column

RIAS
RIAS used Ask the Experts™
on
Hello,
How to set a column which is [Ref] [int] IDENTITY(1,1) NOT NULL to 1 . I tried to delete all rows in the table but still
when I insert a row in the table , theRef value is 33.

Regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Developer / Linux System Administrator / Managing Director
Commented:
That behaviour will be by design. Check out this answer as it will more than likely help: https://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server#21824729

It suggests the following:

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]

Open in new window


For example:
DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

Open in new window


(The above was taken from the question).

Author

Commented:
Perfect! Thanks a lot for your prompt help!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Dont delete all rows.... use truncate instead....

create table ee_test_identity (id int identity, alphanumber varchar(20))

go

insert ee_test_identity
select 'A' + ltrim(number)
from master..spt_values 
where type = 'p'

go

select Min(id) as minID, max(id) as maxID
from ee_test_identity

go

truncate table ee_test_identity

go

insert ee_test_identity
select 'B' + ltrim(1)

go

select Min(id) as minID, max(id) as maxID
from ee_test_identity

go

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks Mark!!!

Author

Commented:
Mark,
I know I have to answer your previous question. Temporarily I have just removed the date columns as pressed for time , but I am gonna get back to you this week.
Hope you understand a developers situation.:>
I really appreciate your time and help.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
No worries, and totally understand :)

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial