Identity Column starting with initial value [Resolved]

Posted by Mandapallishree under Sql Server on 7/31/2013 | Points: 10 | Views : 944 | Status : [Member] | Replies : 3
Hi,

I have a table which contains a column as Identity.
I first inserted some rows and then deleted them.
Again when i am inserting the Id is not getting generated from 1.
So, how i can do it?

Thanks,
Bhavya.




Responses

Posted by: Kmandapalli on: 7/31/2013 [Member] Silver | Points: 50

Up
0
Down

Resolved
Hi,

Since it is a identity column it gets generated by default.
If you need the Id to get generated from starting use this code.

First delete all the rows. Then execute this command:
DBCC CHECKIDENT (tableName, reseed, 0)

When you run this command and insert any row then the Id will be 1.

If you want the Id to be generated from 52, then value should be 51.

MArk as answer if satisfied....

Regards,
Shree M.

Kavya Shree Mandapalli

Mandapallishree, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mandapallishree on: 7/31/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

Thank you so much.
Its working.....

Regards,
Shree M.

Mandapallishree, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/31/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If there is no any Foreign key references to that table, you can use
TRUNCATE TABLE tableName; --To delete all rows and then start identity value with starting value

Some times clients won't give permission to DBCC CHECKINDENT()

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandapallishree, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response