Menu
How can I reset the Identity column of a table to zero in SQL Server?
Edit:
How can we do it with LINQ to SQL ?
743
Reset the Identity Value Using the DBCC CHECKIDENT Method : Here, to reset the Identity column in SQL Server you can use DBCC CHECKIDENT method. Syntax : DBCC CHECKIDENT ('table_name', RESEED, new_value); Note : If we reset the existing records in the table and insert new records, then it will show an error.
Truncate command reset the identity to its seed value. It requires more transaction log space than the truncate command. It requires less transaction log space than the truncate command. You require Alter table permissions to truncate a table.
To change the original seed value and reseed any existing rows, drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values.
DBCC CHECKIDENT (MyTable, RESEED, NewValue)
You can also do a Truncate Table, but, of course, that will remove all rows from the table as well.
To do this via L2S:
db.ExecuteCommand("DBCC CHECKIDENT('MyTable', RESEED, NewValue);");
Or, you can call a stored procedure, from L2S, to do it
100
DBCC CHECKIDENT ( ‘databasename.dbo.yourtable’,RESEED, 0)
More info : http://msdn.microsoft.com/en-us/library/ms176057.aspx
39
Use the LINQ to SQL ExecuteCommand to run the required SQL.
db.ExecuteCommand("DBCC CHECKIDENT('table', RESEED, 0);");
LINQ is a data-source agnostic query language and has no built-in facilities for this kind of data-source specific functionality. LINQ to SQL doesn't provide a specific function to do this either AFAIK.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
