Change rowguidcol property to a different column

In my SQL Server 2008 database, I have a table with two uniqueidentifier columns.

CREATE TABLE MyTable (
    Col1 uniqueidentifier rowguidcol not null,
    Col2 uniqueidentifier not null,
    AttachmentData varbinary(max) filestream null
)

Now, I want to switch the column that has the rowguidcol identifer on it by moving it from Col1 to Col2, so it would end up being

CREATE TABLE MyTable (
    Col1 uniqueidentifier not null,
    Col2 uniqueidentifier rowguidcol not null,
    AttachmentData varbinary(max) filestream null
)

When I try to do this through the table designer (by setting Col1.RowGuid = No and Col2.RowGuid = Yes), I receive the following error:

- Error modifying column properties for 'Col1'.  
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

Is there any way to switch the column with the rowguidcol identifier, or should I rename this table, make a new one, and then copy the data over from the old table?

First of all, the Table Designer is not the wisest of all software and probably does not choose the approach to change the table that you would expect. The designer usually creates a new table, moves the data, then drops the original table. However, based on your error the designer also may not be FILESTREAM savvy.

But if you try it yourself through ALTER commands, you will see that SQL Server cannot alter a column to remove the rowguidcol setting. See what happens:

ALTER TABLE TABLEName ALTER COLUMN Col1 uniqueidentifier DROP ROWGUIDCOL;

Gives the error: Cannot alter column 'Col1' because it is 'ROWGUIDCOL'.

Acknowledgements to Martin Smith. I overlooked the ROWGUIDCOL operators, which are:

ALTER TABLE MyTable ALTER COLUMN Col1 DROP ROWGUIDCOL; 
ALTER TABLE MyTable ALTER COLUMN Col2 ADD ROWGUIDCOL;

However, because of the involvement of the FILESTREAM the DROP ROWGUIDCOL command cannot be processed.

So, yes, you need to create a new table and move the data.

StackExchange Database Administrators Q#72611, answer score: 9