Server RoomThe third community technology preview, or “CTP” of SQL Server Denali was made available for download last week.  It is available for download from Microsoft at http://www.microsoft.com/sqlserver/en/us/future-editions.aspx.

According to Microsoft’s blog, this new version now contains all of the features that will eventually be in the finished product.  The most notable of the new features in this version include a new data visualization tool called “Project Crescent”.  (Break out the checkbook if you’re interested – this tool requires SharePoint 2010 Enterprise.)

If this is your first experience with SQL Server Denali and you have not used either of the previous CTPs, you will immediately notice that Management Studio now uses the Visual Studio 2010 look and feel.  Management Studio contains numerous useful enhancements for developers, including:

1. The ability to specify properties for breakpoints, such “hit counts” (where the environment will only trigger the break point after a certain number of hits) or actions to take on a break point other than stopping execution.

2. Code snippets – pre-defined stubs for common tasks such as creating database objects.

3. A greatly enhanced debugger, including the ability to enter SQL expressions in the “Watch” window.

CTP3 introduces a handful of new SQL functions, including two useful logic functions IIF and CHOOSE.  IIF performs an inline if, very similar to the VB function of the same name.  This function is at least somewhat interesting in that it doesn’t seem to be especially type safe.  This query throws an error:

SELECT iif(1=1, ‘b’, 5)

But this function is successful:

SELECT iif(1=1, 5, ‘b’)

This function is successful:

SELECT iif(1=1, 3, ‘b’+2+‘a’+6)

But this function throws the counterintuitive error “Conversion failed when converting the varchar value ‘c’ to data type int.”  (One would expect the error to be with converting something from the third parameter.)

SELECT iif(1=1, ‘c’, ‘b’+2+‘a’+6)

The moral of this story is to make sure to check your data types – just because it compiles doesn’t mean it’s ready to ship.

The CHOOSE function also performs the same as the VB function of the same name and, amusingly, finally brings to SQL Server a feature that Access and Excel have had since the beginning of time.  CHOOSE is essentially a shorthand for a CASE statement.  This function statement, for instance, will choose the third item in the list of parameters, or ‘c’.  This is useful for converting an enumeration into human-readable values and is visually cleaner than having a lengthy CASE statement.

SELECT choose(3, ‘a’, ‘b’, ‘c’, ‘d’, ‘e’, ‘f’)

What is your favorite feature of Denali?  Let us know in your comments.

image credit: Kim Scarborough

One Comment

  1. V.Panov

    If you need the functionality of Crescent but you don`t want to install the entire Microsoft stack, check this comparison of Microsoft Crescent and Windward AutoQuery. AutoQuery provides all the functionality of Crescent with no server components to install, configure, and administer.

Comments are closed.