Saving Changes is Not Permitted?

In order to test drive the data modeling features of SS2008, I decided to use the engine for a real-world application.  I’m now attempting to use SQL Server to manage the Bateman household.  My first table in the design is a simple list of chores that we need to make sure get done around the house.

As I was putting together some of the details about how I would use this data, I decided to add a new column in order to specify the frequency (in days) which each chore should ideally be performed.  I was quite surprised to get the following error message when tying to add a nullable column to my existing table.

image

Saving changes is not permitted.  The changes you have made require the following tables to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created. 

Are you kidding me?  It wouldn’t let me add an “Allow Nulls” column?  That just seemed absurd so I did a bit more digging.

Apparently, this is now the default behavior for any of the following changes to a table:

  • Adding a new column to the middle of the table
  • Dropping a column
  • Changing column nullability
  • Changing the order of the columns
  • Changing the data type of a column

In order to prevent this default behavior, you simply need to uncheck a box in the table designer options using the Tools -> Options menu item

image

Expand the Designers section to display the Table and Database Designers options.

image

To change this behavior, just uncheck the “Prevent saving changes that require table re-creation” checkbox.

Although this is a bit frustrating, at least it is easy to fix and there is plenty of advice out there.  Here are some other people that found and posted this useful tidbit before me:

http://mirceacimpoi.spaces.live.com/blog/cns!98EC8584C8BBED54!849.trak

http://mtgcsharpguy.blogspot.com/2008/06/ms-sql-server-2008-new-features_13.html

http://www.danrigsby.com/blog/index.php/2008/09/26/sql-server-2008-error-saving-changes-is-not-permitted/

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx

http://geekswithblogs.net/frankw/archive/2008/05/29/saving-table-changes-is-safer-in-sql-server-2008.aspx

Cool New Features in SSMS – Recent Expensive Queries

Depending on whether you are more of a developer or more of a DBA you may care more or less about this next feature.  However, anyone can benefit from it.  SSMS 2008 makes it very easy to crack open the Activity Monitor as there is a noticeable button right on the toolbar.

image

Once inside you get a few charts, as well as some tables of performance related data below.  This is clearly not as exciting on my local Vista machine as it would be on a pounded production database, but you can see where the power of a tool like this could come in handy.

Each of the charts represents these metrics:

  • % Processor Time
  • Waiting Tasks
  • Database I/O
  • Batch Requests/Sec

Probably the most interesting feature to me is the Recent Expensive Queries table.  This allows you to dynamically analyze the most impacting queries against your server.  Since the impact can be determined by a number of factors, it allows you to sort by:

  • Executions
  • CPU
  • Physical Reads
  • Logical Writes
  • Logical Reads
  • Average Duration
  • Plan Count

Even cooler, you can drill into the actual query itself with a right-click option:

image

Cool New Features in SSMS – Select Top 100 Rows

As I have been playing with the newest version of Microsoft’s SQL Server Managements Studio (SSMS), I wanted to share a few of the cool new features.  It seemed that the jump from Query Analyzer in SQL Server 2000 to SSMS in 2005 was not necessarily a smooth one and many people complained about SSMS.  This new version, however, appears to build on the solid framework of 2005 and add lots of cool features that will make our lives much easier.

Select Top 1000 Rows

Just grabbing a sample of data out of a table has been made much easier in 2008:

image

It is also good to see that this has been made configurable in the Tools->Options menu item:

image

You’ll notice that there is also an “Edit Top n Rows” option.  This is arguably less useful as I haven’t found anywhere to specify which 200 rows, but still a noble effort.  I don’t often find myself thinking, “Hmmmm, I think I would like to edit some random data.  Let me just pull up any old record and start changing the data.”  However, once in edit mode, you can always use the “SQL” button (in green box below) to change your query:

image

Non-Numeric Characters in SQL – Follow up

Although my previous post was a bit hasty, I wanted to follow up with some additional comments about the magic in the middle of the UDF created to remove any non-numeric characters from a column in a query result set.  As you can see there is a relatively cryptic body of the function that somewhat resembles a regular expression.  While the solution fit my needs, I have been thinking more about it and realize that the ramifications ripple much more broadly than just being able to strip non-numeric characters out of a string.  My thoughts led me to the following 3 important points to consider:

Important Point #1: LIKE is often underutilized

I have been foolishly using only simple % operations in 99% (blatant overuse of per cent character 🙂 ) of my queries using LIKE.  Most of the time this is plenty of horsepower, but for when you need to dig in a little bit more accurately, it helps to know that additional wildcard characters are supported by the LIKE operator.  In short summation, try a few queries using these additional search options:

  • _ (underscore)
  • [] (range)
  • [^] (not in range)

Important Point #2: PATINDEX() is rad

The real meat of this function is the PATINDEX statement which has the advantage of being able to use wildcards.  This is pointed out with just the right amount of detail in an article by Robert Davis: Pratical Uses of PatIndex.

Important Point #3: Reader beware

One underlying important point is that none of the approaches described here may perform particularly well when working with large volumes of data.  In general, string manipulation should be performed outside of the database.  If dealing with many rows of data, this approach could be pathetically slow.  However, if you are dealing with 1000 records or less you would have to do something pretty extreme to make a query perform unacceptably slow.

Hopefully you will find this useful the next time you are slamming together some ad-hoc SQL to dig through your data.  As mentioned above, this is not recommended for most production applications but can certainly be a handy tool to have in your SQL tool belt.

SQL Server String Manipulation – Removing Non-Numeric Characters

I recently had a need to get a distinct list of numbers from a freeform text field in a database.  After some analysis, my needs were met by the following approach:

CREATE FUNCTION dbo.UFNG_ONLY_DIGITS (@StrVal AS VARCHAR(max))
RETURNS VARCHAR(max)
AS
BEGIN
      WHILE PATINDEX(‘%[^0-9]%’, @StrVal) > 0
            SET @StrVal = REPLACE(@StrVal,
                SUBSTRING(@StrVal,PATINDEX(‘%[^0-9]%’, @StrVal),1),”)
      RETURN @StrVal
END

The function can then be used as follows:

SELECT dbo.UFNG_ONLY_DIGITS(‘8asdf7%87^A8876-*S’)

to return

87878876

Thanks to all who helped in my research with these posts:

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/032f65303d22e343

http://bytes.com/forum/thread81464.html

Minor Detail, Major Impact in SSMS Script Wizard

After a few hours of pulling out what few stubs of hair I have remaining, I finally tracked down a rather perplexing problem relating to the Script Wizard feature of SQL Server Management Studio (SSMS).

My goal was to script out all of the stored procedures in a database in a fully re-runnable state, one SP per .sql file.  This would allow the procedures to be easily checked into source control, and tracked for changes that could be tied to our bug tracking system.  Having stored procedures and other database code in source control can be a tricky proposition because you need the right processes in place to ensure that any changes made to the source code are reflected in the database and migration path.  Conversely, it is equally important to ensure that any changes made directly in the database are accurately and promptly updated in source control.  Sound like a problem any of you have run into?  I know Ben Scheirman has, he posted a great summary of some database migration tools on his blog.

Although there are many good tools out there, my company is taking the “roll your own” approach because we have some specific requirements that no existing tool seems to encompass.  It has to handle SQL Server and Oracle, SPs, triggers, views, as well as ad-hoc DDL and DML changes.  It has been no picnic putting the processes and systems together, but I do think we have a good system in place.

One of the points of our approach is aided in the use of scripting tools, SSMS in particular for the SQL Server based database objects.  If you haven’t uncovered this handy tool (not very discoverable), try right-clicking on your database and following through Tasks to Generate Scripts:

image

I was scripting out a bunch of objects and trying to get home, so I thought I would try a little parallel processing to make the scripting go faster.  When choosing the settings in the SSMS “Generate Scripts” Wizard, I encountered the following differences between machine #1 and machine #2:

machine #1: Good

image

machine #2: Bad

image

Obviously, this smells like a software version difference, so I dug deep into the bowels of SQL Server’s Program Files folder (C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE) to find out that:

Machine #1: SqlWb.exe 2005.90.3054.0

Machine #2: SqlWb.exe 2005.90.2050.0

Unfortunately, Windows Update doesn’t pick up on this difference on 64bit machines!  So, unless you manually update your SQL tools, you’ll be stuck with inferior script generation options.  This is key because the options in the older of these 2 versions is not sufficient IMO.  Running Windows update on a 32bit machine yielded different results, and I was easily able to tell that I needed SQL Server SP2.  Obviously these are very specific situations, but I thought it was worth sharing

Bottom line is that you should have the following very helpful option in the Script Wizard in SSMS:

  • Script Drop

If you don’t check to make sure you’ve got a recent version of the tools.  The previous version only allows you to “Generate CREATE statements only” or “Generate DROP statements only” which is not nearly as useful as tacking the 2 together.  A useful page for quickly searching through SQL Server release details can be found here: http://sqlserverbuilds.blogspot.com/