Database primary key(Identity column )

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Dear,
I am couldn't find out the solution about primary key in my table.explanation,
like I have a table Employee column name EMP_ID(data type int) then it is identity column yes,as so far all is ok. Now problem is when I delete data like it wast 8 data.After Deleting All data, Inserting data it will counted previous & start 9 but I need it will be start from 1.

Any help & idea will be grateful.
thanks in advance.
Mahmud

Posted 11-Jun-12 1:44am

Mahmudunnabi

Add a Solution

Comments

Sridhar Patnayak 11-Jun-12 8:10am

Look at my comment for basic differene between delete and truncate

4 solutions

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)

Sridhar Patnayak · Answer 1 · 2012-06-11T01:48:00

Solution 1

Use Truncate statement behalf of delete

SQL

truncate table Mytable

Thanks,
SP

Posted 11-Jun-12 1:48am

Sridhar Patnayak

Comments

Sandeep Mewara 11-Jun-12 7:50am

How come re-seed of identity will start using Truncate. DO NO suggest such answers that will create further issues for user.

Sridhar Patnayak 11-Jun-12 8:09am

Sandee this is the basic difference between turncate and delete

TRUNCATE will reset any identity columns to the default seed
value. This means if you have a table with an identity column and
you have 264 rows with a seed value of 1, your last record will have
the value 264 (assuming you started with value 1) in its identity
columns. After TRUNCATEing your table, when you insert a new
record into the empty table, the identity column will have a value of
1. DELETE will not do this. In the same scenario, if you
DELETEd your rows, when inserting a new row into the empty table, the
identity column will have a value of 265

Any reference http://codebetter.com/raymondlewallen/2005/05/09/the-difference-in-truncate-and-delete-in-sql-server/

Sandeep Mewara 11-Jun-12 9:10am

Truncate will also clear everything.

I know the difference between DELETE and TRUNCATE. What you are suggesting can lead into data loss.

Re-read what you have shared: "... insert a new record into the empty table...

Maciej Los 18-Apr-13 7:15am

Sandeep, it looks like OP wants to truncate table: when I delete data like it wast 8 data.After Deleting All data, Inserting data it will counted previous & start 9 but I need it will be start from 1

Mahmudunnabi 11-Jun-12 8:13am

thanks a lot I got my answer

nagasiva 11-Jun-12 8:16am

agree with u sridhar 5+

Maciej Los 18-Apr-13 7:15am

+5

Rajesh Kariyavula · Answer 2 · 2012-06-11T01:49:00

Solution 2

Try this:

SQL

DBCC CHECKIDENT('Your Table Name', RESEED, 0)

Hope this helps.

Posted 11-Jun-12 1:49am

Rajesh Kariyavula

Comments

Mahmudunnabi 11-Jun-12 7:55am

RESEED,can u little bit explain about this

Sandeep Mewara 11-Jun-12 8:05am

I gave links explaining the same. Have a look.

Sandeep Mewara · Answer 3 · 2012-06-11T01:52:00

Solution 3

Have a look at the following:
Reset Table Identity[^]
i am applying a identity column on accno in accdetails table[^]
How to reset identity specification in sql server[^]

Posted 11-Jun-12 1:52am

Sandeep Mewara

devsuja · Answer 4 · 2012-06-11T18:58:00

Solution 5

hi,

Truncate table table_name

this will be used for reseeding

Posted 11-Jun-12 18:58pm

devsuja