I want to change the datatype of a column in a table in sql server. I used the following statement:
ALTER TABLE dbo.tbltest
ALTER COLUMN ID uniqueidentifier
But it throws the error
Operand type clash: bigint is incompatible with uniqueidentifier
You cannot convert from an integer to a uniqueidentifier. But you can do it like this.
First delete old data from the table.
Alter the column to some text-format (such as VARCHAR(200)).
ALTER TABLE dbo.tbltest
ALTER COLUMN ID VARCHAR(200)
ALTER TABLE dbo.tbltest
ALTER COLUMN ID uniqueidentifier
To be clear, you can't convert a column from numeric to uniqueidentifier directly, but you can convert numeric to varchar to uniqueidentifier.
You need to add the column first (possibly with a default or nullable so that the column can be successfully added to rows with existing data), update it to the values you want or need, change any keys (if it is a primary key change it to the new column and then also change any foreign keys to this table), then remove the old column and alter the new column as necessary (e.g. setting identity seed, removing nullable, etc.).
Note to people using Entity Framework:
Obviously, if you have a large database with complex relationships, dropping all relationships and ID columns, and re-adding them is a huge ordeal.
It's much easier to update the generated model (.edmx).
Yes, Visual Studio allows you to update the data type without complaining. In fact, when you save the .edmx file, the relationships will be validated; any fields that you may have missed will show up as errors.
After you're finished, you can regenerate the database from the model. Boom, done.
If Singh's answer is not working then you probably have to drop the the table and recreate it.
I disagree with the other answers that state that this can't be done without dropping data. In fact @Lucero has already eluded to the correct way to do this in his comment.
You most certainly can convert a BigInt to a UniqueIdentifier (and back again if you wish.) You DO NOT need to drop the table, truncate the data, or do anything more radical then run a few commands. It takes two steps:
ALTER TABLE dbo.tbltest ALTER COLUMN [ID] VARBINARY(8)
ALTER TABLE dbo.tbltest ALTER COLUMN [ID] UNIQUEIDENTIFIER
Now keep in mind that only the front half of the generated GUID will be non-zero values because a BitInt value is 8 bytes and a UniqueIdentifier is 16 bytes, so you'll end up with Guid's that look like:
E38E4965-8DFC-4FF3-0000-000000000000
