Geeks With Blogs
Frank Wang's inspirations on .NET
IEnumerable<Inspiration> inspirations = from i in DataContext.Inspirations where i.Sharable == true select i

I have been playing with SQL Server 2008 "Katmai" for quite a while. Although the new management studio designer looks almost the same as that of SQL 2005, there're actually many new features under the hood, and some of them are quite thoughtful. I have just been discovering these goodies here and there everyday.

What I found today is also pretty interesting. By default, SQL Server 2008's designer no longer allows saving changes that require table re-creation. You will need to turn off this safety check manually if you enjoy the flexibility of saving table changes directly from the designer. This feature may be a little annoying for your day to day development work, but it definitely makes your data safer if you are working with tables with a huge amount of data.

To demonstrate this, we will try to add a new column in the Northwind.Suppliers table, and then attempt to save the change, as shown in the screen shot below.

image

You get immediate warning message saying the saving changes is not permitted. That makes sense. Adding a new column to a table obviously requires table re-creation. Now go to Tools --> Options --> Designers --> Table and Database designer. You should see a new option "Prevent saving changes that require table re-creation" is checked. 

image 

Simply uncheck this option if you still want to be able to save table changes from the designer.

Posted on Thursday, May 29, 2008 3:36 PM SQL Server 2008 | Back to top


Comments on this post: Saving table changes is safer in SQL Server 2008

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
Frank,
do you use Express or Enterprise and does SQL2008EXP contain SSMSEE 2k8?
Left by Alexey Kucherenko on May 30, 2008 11:47 AM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
Nice article, Frank - saved me some time!

Left by Paul Smietan on Oct 28, 2008 2:54 PM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
This feature has been driving me nuts. Seems to me it should just go ahead and perform the action if 0 records exists! Or at the very, very least cancel the action and return to the designer. But NO! You have to lose all your changes!

Thanks for the tip, I've disabled it.
Left by Tolga on Jan 05, 2009 4:45 PM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
May be a safer idea, but not user-friendly implementation. see http://www.west-wind.com/Weblog/posts/455560.aspx
Left by Michael Freidgeim on Apr 13, 2009 4:08 AM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
It seems you have to uncheck the option in every session, i.e. it doesn't get saved.
Left by Tony Nunn on Sep 17, 2009 6:59 AM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
I also fed up with that error. Thank u so much for your article. Do the good work ahead.
Left by Pubudini Prasanna on Oct 27, 2009 11:20 PM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
You did a great job on this post. I implemented your solution to configuring alerts and I have a question. How do you get useful data in your email?
Left by sexy corsets on Oct 29, 2009 11:54 PM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
Left by Bulk Sms on Oct 30, 2009 1:37 PM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.
Left by plumber mascot on Nov 18, 2009 12:19 AM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
msg91 is a Bulk SMS Solution provided from India. This is an online web base SMS Portal at very cheap price
which can be used all over world to send bulk sms.
for bulk sms service click here:
Bulk SMS
Left by Bulk SMS on Aug 17, 2010 7:34 AM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
Is it me, or has Microsoft gone out of their way lately just to make their software annoying? Case in point...the way Office 2010 is layed out. (I'm still hating it).
Left by Los2000 on Mar 08, 2011 2:29 AM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
I've post a suggesstion to http://connect.microsoft.com/SQLServer/feedback/details/432179/change-default-prevent-saving-changes-that-require-table-re-creation
Left by Michael Freidgeim on Jun 22, 2011 6:53 PM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
This information is realy helpful for all.


Regards,


Ananth Prasath. P

Chief Executive Officer | KAP Computer Solutions Pvt. Ltd.,

Mobile: +91-97380 10000 | +91-97380 23400 (Support) | Email: info@kapsystem.com | Web: www.kapsystem.com
Left by kap computer solutions on Jun 24, 2011 7:10 AM

# re: Saving table changes is safer in SQL Server 2008
Requesting Gravatar...
tank you for help
اجرت با آقا امام زمان(عج
صلوات
Left by mohammad reza on Oct 27, 2011 3:22 AM

Your comment:
 (will show your gravatar)


Copyright © Frank Wang | Powered by: GeeksWithBlogs.net