Hello friends,
I have a table in SQL server 2005. it contains one identity column named EmpID
it's datatype is int isidentity and auto increment by 1.

I deleted all the records from this table.
Now I want that EmpID should start again from 1 how can I do it ?


Thanks & Rgds,
Kiran Suthar.

DBCC CHECKIDENT 
( 
'table_name'
[ , {
NORESEED | { RESEED [ , new_reseed_value ] }
}
]
)
[ WITH NO_INFOMSGS ]

Dbcc checkident() will allow you to reset the seed value.

If your table does not have any constraint, you can use "truncate table" to quickly delete data and reset the seed in one step.

KiranSuthar, please update thread.

Thanks,

Derek

TRUNCATE TABLE "table_name"

will delete all the records as well as it resets the identity column to 0. simple way to solve your problem

Difference between Delete & Truncate visit

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.
TRUNCATE TABLE cannot used be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. If all table rows need to be deleted and there is a foreign key referencing the table, you must drop the index and recreate it. If a TRUNCATE TABLE statement is issued against a table that has foreign key references, the following error is returned:

DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary. You can also specify a WHERE clause to narrow down the rows to be deleted. When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).
When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following statements can be used to achieve the same result as TRUNCATE TABLE:

• DELETE from "table_name"
• DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

Hope this helps!

KsMani

MCDBA

Truncate is the easiest and best way to do this if you are going to delete the data anyway. reseed if you are trying to keep data in the table while you reset the id seed.There are only a few considerations when truncating and they are highlighted in Books Online. If none of those considerations apply, truncate is the way to go. I concur with SQL Engineer..

Thank you all of you who has replied to my question.
My problem is solved.

Thanks,
Kiran

You can use truncate statement to reset table...

truncate table TableName

You can also use below statement to reset identity column...

DBCC CHECKIDENT ('TableName', RESEED, 0)

 

• Edited by pka246 1 hour 43 minutes ago