reset identity after delete in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

i have one question about sql when we delete one row suppose 102 and then if i insert then that inserted record should be 102 so i want to reset my identity each time ? help me......

Posted 31-Jul-13 19:29pm

Dholakiya Ankit

Add a Solution

Comments

Bernhard Hiller 1-Aug-13 2:28am

Waht is the reason for that requirement?
I'd prefer to have the database engine handling the ID values: that's just something internal to the mode of working of a relational database, and nothing to be shown to the user. Just imagine a different type of storage which does not allow you to see its internal identification methods at all, handling it automagically for you...

Dholakiya Ankit 1-Aug-13 2:33am

nice hiller thanks fr advice

3 solutions

Solution 2

after delete the row from table, you have to reset the identity of the table as

DBCC CHECKIDENT (yourtable_name, reseed, 101)

then your next record inserted with an identity 102

http://www.dotnetbysatya.co.nr

Posted 31-Jul-13 19:53pm

Member 9563022

Comments

Dholakiya Ankit 1-Aug-13 2:14am

nice one

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeBlack · Accepted Answer · 2013-07-31T19:38:00

Solution 1

Hi Ankit,

You can create one function which will accept the name of the table as a parameter.

The functionality of that table is to identify the missing id.

For Example :
In my table 'Test' i have Ids : 1,2,3,5,6

Now when I call that function MyFunction('Test'), this function should return 4 as an identity.

And you can use that returned id.

ForExample :

INSERT INTO
Test
(Id,FirstName,LastName)
VALUES
(MyFunction('Test'),'aaa','bbb')

Posted 31-Jul-13 19:38pm

CodeBlack

Comments

Dholakiya Ankit 1-Aug-13 2:13am

yes good one but should be sequence 1,2,3,4,5,6 with out function would be great

CodeBlack 1-Aug-13 2:18am

As per my knowledge, I dont think that without using any function we can achieve this.

Bernhard Hiller 1-Aug-13 2:25am

Dangerous: what about two clients trying to insert a new row at the same time?

CodeBlack 1-Aug-13 2:43am

simple, set the identity of the column to true :) Sql itself handles the identity and two requests come at same time than thread will be handled :)

Dholakiya Ankit 1-Aug-13 2:33am

okay get it

Dholakiya Ankit · Accepted Answer · 2013-08-01T00:18:00

Solution 3

i accept that its not good idea to reset identity.......::)

Posted 1-Aug-13 0:18am

Dholakiya Ankit