How to remove identity column from table

Question

I have created a table with employee id as identity column. Now, I want to remove identity and replace datatype as bigint. I am using Sql Compact edition. How to achieve this?

Answer 1

In "real" SQL Server, you'd have to do these steps - not sure if SQL Server CE allows the same, but give it a try! I'm assuming you probably have your PRIMARY KEY constraint on that column, too - right? If not, you don't need to do the first and last step. And I'm assuming you want to have the IDENTITY on the column again, right?

-- DROP the primary key constraint (if you have that on your column)
ALTER TABLE dbo.Employees 
DROP CONSTRAINT PK__Employees__3214EC274222D4EF 

-- ALTER the datatype into BIGINT
ALTER TABLE dbo.Employees 
ALTER COLUMN Employee_ID BIGINT 

-- set PK constraint again  
ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY(Employee_ID)

Answer 2

This should help - http://www.sqlmag.com/Files/23/22081/Listing_03.txt

Answer 3

I don't believe you can, using TSQL, remove the IDENTITY property from a column. Instead:

1. Add a new BIGINT column to the table
2. Copy the data from the current IDENTITY field into the new field
3. Drop the existing column
4. Rename the new column to the correct name

You can do it in SSMS in the Design view for the table. I believe behind the scenes it does something like above.

Update: To confirm, in SSMS 2K8 when you try to remove the IDENTITY property from a column and then save it, it will actually recreate the table (you can see what it does exactly by monitoring in SQL Profiler). In order to do it in SSMS, you need to ensure you have the "Prevent saving changes that require table re-creation" option turned OFF in Tools-> Options -> Designers -> Table and Database Designers. I think it defaults to ON, which would result in an error message when you try to do it otherwise.