Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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.

share|improve this answer
    
This isn't the case. The ROWGUIDCOL can be removed from/added to columns. –  Martin Smith Jul 28 at 20:38
1  
Not when the table has a filestream column. Attempting to drop the ROWGUIDCOL will induce the error: A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column. They will have to copy to a new table. –  wBob Jul 28 at 20:41
    
@wBob - Of course! That makes sense. Unfortunately my hasty DV is locked in unless this answer is edited. But it could do with editing anyway to acknowledge the existence of ALTER TABLE MyTable ALTER COLUMN Col1 DROP ROWGUIDCOL; ALTER TABLE MyTable ALTER COLUMN Col2 ADD ROWGUIDCOL; –  Martin Smith Jul 28 at 20:43
    
I also had a go with a Database Project as its ALTERs are sometimes smart, and an sp_rename column option but neither worked. –  wBob Jul 28 at 20:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.