Click here to Skip to main content
15,944,390 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
Sridhar Patnayak 11-Jun-12 8:10am    
Look at my comment for basic differene between delete and truncate

Use Truncate statement behalf of delete
SQL
truncate table Mytable


Thanks,
SP
 
Share this answer
 
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
Try this:

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


Hope this helps.
 
Share this answer
 
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.
hi,

Truncate table table_name

this will be used for reseeding
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900