Robert Davis (10/5/2011)
First thing, a single transaction is better.
That's what I think as well. The only issue is length of transaction, but consistency overrides concurrency in my opinion.
Secondly, I recommend renaming the old table instead of dropping immediately just in case.
That's what I think as well. I just posted what is created by SSMS when you script the change made in the GUI.
Third, are there indexes, triggers, etc. that also need to be created on the new table?
I didn't add any indexes to the table in my example just to keep it simpler and shorter. If there are indexes, script changes does include the indexes. I haven't tried with a trigger yet. I'll have to check on that. I don't think there are any triggers on any tables I have to make the changes on.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question