March 21, 2012 at 8:12 am
Hi Everyone,
Hope all is well.
I was trying to make changes to the definition of a table from the SSMS UI, a column from a table now needs to allow NULL from Not NULL. However SSMS doesnt let me do this from the UI. Whenever I try to make changes I get the below message:
Saving changes is not permitted. The changes you have made require the following tables to be dropped and recreated. You have either made changes to a table that cant be recreated or enabled the option prevent saving changes that require the table to be recreated.
However if I do the same thing through:
ALTER TABLE TEST.DBO.TABLE_1 alter column ID nchar(10) null;
It works without any issue.
Has any one seen this issue before? Need your inputs.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
March 21, 2012 at 8:19 am
There is an option you have to change to in SSMS.
Tools -> Options -> Designers -> Table and Database Designers
March 21, 2012 at 8:28 am
Thanks for the reply Lynn.
The option "prevent saving changes that require table re-creation" is currently checked. But I dont want any data loss if I am making changes to the definition. With the Alter statement I am not recreating the table. So if I uncheck this option Would it delete data in my table when recreating the table?
Thanks again
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
March 21, 2012 at 8:31 am
Try it in a sandbox database with a table you don't care about.
March 21, 2012 at 8:32 am
The way SSMS works in many situations is to create a temporary table and import the data, recreate the actual table and import the data back in.
For precise control you are much better off scripting it yourself, especially with large tables.
March 21, 2012 at 8:32 am
Sapen (3/21/2012)
Thanks for the reply Lynn.The option "prevent saving changes that require table re-creation" is currently checked. But I dont want any data loss if I am making changes to the definition. With the Alter statement I am not recreating the table. So if I uncheck this option Would it delete data in my table when recreating the table?
Thanks again
that's the thing about SSMS; even though it's possible to simpyl issue an ALTER statement, it's typical behavior is to do the following:
create a new table with the new definition with a temporary name.
migrate the data to the new tables
script the constraints from the old table to the new , dropping them fromt he old and creating them on the new
dropping the original table
renaming the temp table to the correct name.
it does that because if there were dependant objects that were affected, it makes it easier to script all of them in a standard way, evne if it's not the easiest way.
Lowell
March 21, 2012 at 8:44 am
Cool...it warns me. It wont recreate the table just like that when I have data.
Thanks Lynn.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
March 21, 2012 at 8:47 am
Mystery Limbo and Lowell thanks for the info. I was not aware of this.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
March 21, 2012 at 8:49 am
One of the key advantages of using scripts to update table definitions, is that the GUI will do what's been described here, and that can take a long time or even crash a server if you need to make changes to a very large table. The duplicate copy can overload tempdb pretty easily on a big table, and the resources and locks used in copying a big table (twice, actually) can also be problematic even if you don't run out of disk space.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2012 at 8:52 am
GSquared..That goes straight into my performance hit checklist.
Thanks much
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
March 21, 2012 at 9:15 am
It's not just a performance thing either. Once you have a script working, you can reuse it as many times as necessary, and you can easily promote changes through development, test and live environments. You can also modify your scripts to make any number of similar changes. Your documentation becomes a lot simpler, too: "run this script" is much easier to understand than a series of instructions for a GUI.
John
March 21, 2012 at 9:28 am
Sure John. I usually prefer the scripting approach as well because I can store them in my repository as they are changes to structure of a table.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
March 23, 2012 at 7:39 am
If you do all your DDL through scripts, and store them in source control with time-stamps, you can actually return a database object to any particular point-in-time for its schema, by running the scripts sequentially from the original Create script up through the point you want. Very useful when you need it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply