varbinary(max) columns

TOAD for SQL Server 6.0:

I am attempting to alter a table with a varbinary(max) column in SQL Server 2008 R2. I consistently get the following error message:

If I alter the table manually, through the editor, it works fine. The first time, I was trying to add the column to the table. Now, I am trying to add another column. I am moving the column up from the end of the table, so it is doing a table copy. If I make the column another data type, say an int, it works without issues.

OK. I figured out the problem. I needed to convert my Clustered Index to a non-clustered index. I’m stil puzzled why it worked fine in the editor, but not in the Alter Table form.

So, I guess the moral of the story is, when you set up a varbinary (or varbinary(max)) column, make sure you also set all your indexes to non-clustered. Otherwise, SQL Server has problems and throws very unhelpful, opaque errors.

Upon further testing and investigation, I discovered the actual real culprit to my travails and issues. A trigger that was on the table. Everytime I was using the alter table form in TOAD, it was creating a new table, and copying the data from the old table in. The trigger was not written to handle varbinary columns. Once I fixe dthat, the problem went away, and I changed the index back to clustered.

The reason it looked like the index was the issue, is that when I ran the index change script in an editor window, the trigger was dropped, but not recreated. I missed error, as it went by too fast. So, after the trigger was gone, everything worked - and it looked like the clustered index was th issue.