Reset Identity column to zero in SQL Server?

Question

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 ?

Enumerate

Answer 1

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

Wivern

How can we do it with LINQ to SQL ? – Enumerate

Thanks dude, But I have a problem in executing the above command. I've choose a name for my table in UTF-8 format(Persian) and then I insert it instead of MyTable the following error is occurred Cannot find a table or object with the name "[????? ?????? ???]". Check the system catalog. How can I resolve it? – Enumerate

Answer 2

DBCC CHECKIDENT ( ‘databasename.dbo.yourtable’,RESEED, 0)

More info : http://msdn.microsoft.com/en-us/library/ms176057.aspx

Whiffle

How can we do it with LINQ to SQL ? – Enumerate

@Mohammad: execute that SQL statement with context.ExecuteCommand – Florence

Answer 3

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.

Marleah

Answer 4

use this code

DBCC CHECKIDENT(‘tableName’, RESEED, 0)

Attorneyatlaw

This answer doesn't add anything that wasn't already contained in the previous answers, please consider deleting. – Tye

Answer 5

Generic extension method:

    /// <summary>
    /// Reseeds a table's identity auto increment to a specified value
    /// </summary>
    /// <typeparam name="TEntity">The row type</typeparam>
    /// <typeparam name="TIdentity">The type of the identity column</typeparam>
    /// <param name="table">The table to reseed</param>
    /// <param name="seed">The new seed value</param>
    public static void ReseedIdentity<TEntity, TIdentity>(this Table<TEntity> table, TIdentity seed)
        where TEntity : class
    {
        var rowType = table.GetType().GetGenericArguments()[0];

        var sqlCommand = string.Format(
            "dbcc checkident ('{0}', reseed, {1})",
            table.Context.Mapping.GetTable(rowType).TableName, 
            seed);

        table.Context.ExecuteCommand(sqlCommand);
    }

Usage: myContext.myTable.ReseedIdentity(0);

Obscure

Is TIdentity necessary? – Clitoris

You saved my day dude. Thank you. :) – Patrinapatriot

Answer 6

To accomplish the same task in a SQL Compact table use:

db.CommandText = "ALTER TABLE MyTable ALTER COLUMN Id IDENTITY (1,1)";   
db.ExecuteNonQuery(  );

Foresail