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?
Best Answer
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:Gives the error: Cannot alter column 'Col1' because it is 'ROWGUIDCOL'.
Acknowledgements to Martin Smith. I overlooked the ROWGUIDCOL operators, which are:
However, because of the involvement of the
FILESTREAM
theDROP ROWGUIDCOL
command cannot be processed.So, yes, you need to create a new table and move the data.