How to change column's type in SQL Server CE?

Question

I would like to change the type of a table's column in a SQL Server Compact Edition database, but it seems that the statement

ALTER TABLE [table name] modify [column name] [new type]

isn't working (VS2010 gave me the error : "The ALTER TABLE SQL construct or statement is not supported.").

My original type is INT and I would like to change it to a BIGINT.

Is it completely unsupported by SQL Server CE or do I have to execute another kind of query ?

Answer 1

Seems in fact that Visual Studio was the origin of my problem, by using CompactView program, the query

ALTER TABLE [table name] ALTER COLUMN [column name] [new type]

worked and solved my problem. :) Thanks a lot for your help !

Answer 2

Don't forget, that you can not ALTER COLUMN if data in it conflicts with new COLUMN type. For exemple: COLUMN type is VARCHAR2 and there are symbols and you want to ALTER to NUMBER. Or data type is VARCHAR2(20) and you want to change to VARCHAR2(5) and there are strings longer than 5 simbols.

Answer 3

You haven't stated what exactly "isn't working" means, but according to the Books Online, the syntax should be ALTER TABLE [table name] ALTER COLUMN [column name] [new type] just like any other instance of SQL Server.

You'll run into problems, however, if the data in the column isn't supported by the new data type.

Answer 4

An alternative would be to create a new table and then insert the values of the current table into the new one and then just drop the old one. Then rename the new one.