Reseed Identity Column in SQL Compact

Reseed identity column in SQL Compact

ALTER TABLE [MyTable] ALTER COLUMN [Id] IDENTITY (1,1)

Updating the seed value on an IDENTITY column in SQL Compact 3.5?

The problem was our false assumption that that the identity column is always the first one in the table. So, the error message came because we tried to make a column as identity which wasn't actually an identity column before.

Reset Identity column to zero in SQL Server?

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

perform reseed the ID column of a table

There are workarounds.
Check this out...

It looks like programmatically you can Alter Column - not interactively.
Or you can drop and recreate if you don't need the data.

http://social.msdn.microsoft.com/Forums/en-US/871f38d9-8c77-4050-b5dd-ebab1b88b330/sql-server-compact-reseed-identity-column

Reset AutoNumber(Identity Field) in Database

DBCC does not exist in CE, but you can use this instead:

ALTER TABLE t1 ALTER COLUMN id IDENTITY (1,1)

IDENTITY_INSERT and SQL Server Compact

You need to reset the seed like this

ALTER TABLE [Foo] ALTER COLUMN [Id] IDENTITY (4, 1)

SQL Ce 4 - How can I run DBCC CHECKIDENT

There is no DBCC CHECKIDENT in SQL CE 4.

You need to use ALTER TABLE.

ALTER TABLE [MyTable] ALTER COLUMN [IdentityColumn] IDENTITY (999,1).

---

Related Topics