Can not change table's structure 1

[markros]

Hi everybody,

I'm trying to change the table to have the ID field as GUID (it was int Identity field). However, then I try to save it I'm getting an error preventing me saving changes. I also see a table with the same name and _tombstone prefix (two fields - ID and DeletionDate).

What should I do to be able to change the table structure?

I already truncated this table.

Thanks in advance.

 

[RiverGuy]

1. Script out your table definition to a new query window
2. Change the column's definition to GUID (be sure to add a default if needed)
3. Rename the old table
4. Run the SQL script to create the new table.
5. Copy the data from the old table into the new table.
6. Once you are satisfied with the results, drop the old table.

 

[markros]

Thanks a lot, this worked. Wondering why SSMS is not smart enough to do this by itself.

 

[RiverGuy]

If you had an Identity, you using one of the number data types. So if you simply change the type to GUID in SSMS, it's not going to be able to convert an Int to a GUID. Perhaps if you had gone into the table designer, deleted the Identity column, and added it again as a GUID it would have worked.

 

[markros]

No, it didn't work this way either.

But now I already re-created my table, just didn't specify the PK. Now I want to specify the ID field as a PK in the designer and again it doesn't let me ((

The table has triggers, so I would have to do everything through the scripts again?

 

[RiverGuy]

What version of SQL Server are you using? SQL Server 2008 SSMS doesn't allow you to do as much (at least by default) via the designer as far as table changes are concerned. But that could be a good thing. You get to become more proficient at writing DDL.

ALTER TABLE YourTable ADD CONSTRAINT pk_YourTable  PRIMARY KEY(YourUniqueIdentifierColumn)

 

[markros]

Ok, interestingly, when I tried to right click on the field in the design table window and set it as primary this way, it didn't let me, but when I tried to do the same using indexes button at the top toolbar, it let me. I would expect these two dialogs behave exactly the same.

Looks like some internal SSMS bug to me in SQL Server 2008 Express.

 

[markros]

Well, looking on the bright side of the things I did become slightly more proficient in writting DDL. Now, on the other hand, I lost almost 40 minutes (or more) on this exercise. Not sure what outweight

Anyway, thanks a lot again.

 

[markros]

I found the option in SQL Server 2008 to change this behavior (menu Tools\Options\Designers), but I don't think I would change this option (the last one).