Frank Wang's inspirations on .NET

IEnumerable<Inspiration> inspirations = from i in DataContext.Inspirations where i.Sharable == true select i

Saving table changes is safer in SQL Server 2008

Thursday, May 29, 2008 3:36 PM

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.



  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Feedback

# re: Saving table changes is safer in SQL Server 2008

Frank,
do you use Express or Enterprise and does SQL2008EXP contain SSMSEE 2k8? 5/30/2008 11:47 AM | Alexey Kucherenko

# re: Saving table changes is safer in SQL Server 2008

Nice article, Frank - saved me some time!

10/28/2008 2:54 PM | Paul Smietan

# re: Saving table changes is safer in SQL Server 2008

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. 1/5/2009 4:45 PM | Tolga

# re: Saving table changes is safer in SQL Server 2008

May be a safer idea, but not user-friendly implementation. see http://www.west-wind.com/Weblog/posts/455560.aspx 4/13/2009 4:08 AM | Michael Freidgeim

# re: Saving table changes is safer in SQL Server 2008

It seems you have to uncheck the option in every session, i.e. it doesn't get saved. 9/17/2009 6:59 AM | Tony Nunn

# re: Saving table changes is safer in SQL Server 2008

I also fed up with that error. Thank u so much for your article. Do the good work ahead. 10/27/2009 11:20 PM | Pubudini Prasanna

# re: Saving table changes is safer in SQL Server 2008

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?
10/29/2009 11:54 PM | sexy corsets

# re: Saving table changes is safer in SQL Server 2008

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.
10/30/2009 1:37 PM | Bulk Sms

# re: Saving table changes is safer in SQL Server 2008

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. 11/18/2009 12:19 AM | plumber mascot

# re: Saving table changes is safer in SQL Server 2008

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 8/17/2010 7:34 AM | Bulk SMS

# re: Saving table changes is safer in SQL Server 2008

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). 3/8/2011 2:29 AM | Los2000

# re: Saving table changes is safer in SQL Server 2008

I've post a suggesstion to http://connect.microsoft.com/SQLServer/feedback/details/432179/change-default-prevent-saving-changes-that-require-table-re-creation 6/22/2011 6:53 PM | Michael Freidgeim

# re: Saving table changes is safer in SQL Server 2008

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
6/24/2011 7:10 AM | kap computer solutions

# re: Saving table changes is safer in SQL Server 2008

tank you for help
اجرت با آقا امام زمان(عج
صلوات 10/27/2011 3:22 AM | mohammad reza

Post a comment