Tip: How to reset the identity column in a sql server database

Tip: How to reset the identity column in a sql server database

  
Hi, I had to reset the identity column to 0 again for one of my applications. This was necessary because one of the other attributes of the entity somewhat depended on the identifier (probably that should not have the case at all). This can be done with the following command in the query screen:

dbcc checkident ('<table_name>', reseed, 0)

If you only want to know what the current identifier value is of the latest created row (still existing or not), you can use:

dbcc checkident ('<table_name>')

Cheers, Annemarie
Nice to know.

Although probably one of the golden rules related to a primary key has been violated.

Keep in mind that doing a reseed may result in broken references. Although SQL Server (since 2000) supports cascade updates, it's a propery on the foreign key reference. In other words, be aware of a possible reference integrity damage.

Marco

Hi

This is kind of old post but. . .


When I try this:

DELETE FROM {Commission}; DBCC CHECKIDENT ({Commission} , reseed, 0);

I am getting

(DELETE FROM {Commission}; DBCC CHECKIDENT ({Commission} , reseed, 0);  ): Incorrect syntax near '.'.


Any ideas?


Hi Hakan

Where are you executing those commands? 

Are you in an advanced query in your eSpace or in SQL Server Management Studio?

You have to execute DBCC CHECKIDENT command directly in SQL Server Management Studio.

The table name must be between ' ' and must be the physical table name.

First execute the query bellow to find physical_table_name:

select physical_table_name
from ossys_entity
where name='Commission'

Then execute:

DELETE FROM <physical_table_name>; DBCC CHECKIDENT ('<physical_table_name>' , reseed, 0); 

I hope it helps.

Cláudia



Also tried that but still not working:

SyntaxEditor Code Snippet

DELETE FROM {Commission}; DBCC CHECKIDENT ('{Commission}' , reseed, 0);

Hakan

I think you cannot do a DBCC CHECKIDENT in an advanced query in Service Studio.

You should do that in a SQLServer client application like SQL Server Management Studio. If you don't have it instaled, you can download SQL Server 2012 Management Studio Express from here:

http://www.microsoft.com/en-us/download/details.aspx?id=29062

Cláudia

why not simply delete the table and recreate it?


I am doing data migration-and-import and I have to verify the data integrity in every step.

So I need to reinitialize the tables and perform the reload with a single click.

Anything that can be done from a web browser is acceptable. 

If I have to switch screen go there delete the table, go to another place to deploy the application to create the table, then go to another solution to load the data... 

I don't want a solution. I want the solution from a single place. 

With Outsystems, anything less is unacceptable.