Reset IDENTITY(1,1) column

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
RIASAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Perfect! Thanks a lot for your prompt help!
0
Mark WillsTopic AdvisorCommented:
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

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

RIASAuthor Commented:
Thanks Mark!!!
0
RIASAuthor 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.
0
Mark WillsTopic AdvisorCommented:
No worries, and totally understand :)
0
RIASAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.