Removing identity property is something I have seen many people doing for some reason.

Most of the time, they perform an additional query to get the next value and then pass it. This method creates additional locks and will not work perfectly in a multi user environment.

I believe identity is one of the good things SQL Server has.

Having said that, there is another way of moving them. It will work better if you do not have foreign keys defined against this table and this table is large.

As of now, it will work only in enterprise edition of SQL Server 2005, but I believe it will work in some additional editions of SQL Server 2008

1. Create another table with the same structure except the identity property.

2. Switch the data from old table to new table

3. Drop the old table (which has the identity property)

4. Rename the new table with proper name.

In case you have soem foreign keys you need to drop the foreign keys and re-create them.

I heard that SQL Server mobile edition has a command where you can simply turn off the identity property, but it is not yet available on other editions.