SQL Server 2008 Prevent Saving Changes Setting

SQL Server 2008 has a setting that PREVENTS saving certain table schema changes (such as changing column order) – and this setting is ENABLED by default.

When I first encountered the effects of this setting, I was attempting to rearrange the column order of a table. Using the GUI, I went to the table design view and moved one column in front of another.

When I hit ‘Save’, I received this message:

When a table’s schema is changed, the process that SQL Server performs drops the table and recreates it. The new setting that prevents this is called ‘Prevent saving changes that require table re-creation‘, and it’s easy enough to turn off. I’m sure it has been put in place as a safeguard to prevent unintended table changes. To turn off the setting, go to (on the menu bar) Tools/Options/Designers/Table and Database Designers, then under the Table Options section, uncheck Prevent saving changes….

Schema changes will not be blocked.  You’re welcome.

HOW TO GET CONDITIONAL SUM AGGREGATES IN A RECORDSET USING THE CASE STATEMENT

Today I was presented with a request to find a SQL Server-compatible substitute for the IIF function that MS Access uses. The IIF function is a conditional function; it evaluates a condition (test for TRUE or FALSE) and returns one configurable value or another depending on the result of the evaluation. SQL Server does not have the IIF function, but it can easily perform the same operations using a CASE statement. The scenario I was shown today used the IIF function wrapped in a SUM function, effectively adding a ‘1’ for each record having a certain value in the evaluated field.

To demonstrate how SQL Server can solve this problem, I’m going to use the AdventureWorks database and the Production.Product table. This table has a Color field. I want to find (in one query) the number of records for each respective color (or no color: NULL). Here’s a quick shot of part of the table:

I can see that there are the colors Black and Silver in the table, along with many NULLs. To get the count of records having the color Black, I can run the following:

That gives me a count of 93 records. But how many different colors are there? To find out, I’ll get all the DISTINCT Color values:

NOW I can formulate my entire query accurately, getting a total count, NULLS count, and counts for every color:

Looks good!

Access IIF statement
CASE statement

SHORTCUT TO START SQL SERVER MANAGEMENT STUDIO (SSMS)

Instead of mouse-clicking through Start/Programs/Microsoft SQL Server 2005/SQL Server Management Studio Express, just open up the Run menu by clicking Start/Run or using the keyboard shortcut Windows Key + R, then type ssmsee (for Express Edition) or ssms (for full versions).

That was easy!

There are also options for the command, such as -nosplash, which will start Management Studio without the splash screen, or -E, which will start SSMS and log you in automatically with Windows Authentication (albeit without the Object Explorer connection).

More info and options here: http://msdn.microsoft.com/en-us/library/ms162825.aspx