Although many of the bigger advances and improvements in the next SQL Server version, SQL Server Denali, are solidly geared toward business development, manageability, and high availability, there are some new features and enhancements to SQL Server Denali that developers will find useful. To that end, I wanted to take a two-part look at Denali and what it will mean for developers
SQL Server Denali
Even though I have no idea when SQL Server Denali will ship (it's still technically in CTP at the moment, so I'm guessing it will probably be released in 2012), I'm excited about a number of new features and capabilities that it will offer.
To that end I've been spending small amounts of time here and there playing with Denali in my lab. Installing and deploying the Denali CTP is fairly straightforward, but if you're not in a position to throw enough time at it to install things on your own, then I highly recommend taking a look either at Books Online for SQL Server Denali, or at taking a peek at Aaron Bertrand's (blog | Twitter) blog. He's done a lot of blogging (and presenting) on the subject and has a wealth of materials available for anyone curious about Denali.
That said, much of his content (but not all) is geared toward DBAs. So, in my first installment of these two articles on Denali, I therefore want to focus just on features and benefits that developers will find useful.
SQL Server Management Studio Enhancements
One of the cool things that developers will notice about SQL Server Denali is that it finally addresses a number of deficiencies that have always made SQL Server Management Studio (SSMS) feel like a second-class citizen in the "studio" world. Here's a quick overview of just some of these new features that I really like.
Clipboard cycling and multi-monitor support. Visual Studio 2010 added robust multi-monitor support—and the first thing you note when firing up SSMS is that it "derives" from Visual Studio 2010 in looks, skinning, and so on—as shown in Figure 1.
Figure 1: SQL Server Management Studio Denali, which picks up Visual Studio 2010 styling and UX
Accordingly, that means that SSMS Denali picks up the same great support for multiple monitors. To test it out, just grab a window (like the Object Explorer) or an existing tab in the code editor (i.e., like a .sql file that you're working on) and drag it out into a different monitor—where you'll see it stick and work just as you'd anticipate.
Likewise, one way in which SSMS has felt like a second-class "studio" citizen over the years has been in how its clipboard only remembers a single thing at a time, unlike Visual Studio (which has supported the ability to "cycle" through objects in your clipboard/clipboard ring for years now). Personally, I haven't found the lack of a clipboard ring in SSMS to be too much of a burden—except for those points once every week or so when I fat-finger Ctrl+C when I actually meant to paste something (meaning that I now end up with either a "blank" clipboard or some stuff I don't want, and I can't "cycle" back to what I had before).
Nonetheless, being able to cycle through various items in the clipboard (by using Shift+Ctrl+V) represents just one more way that SSMS Denali will feel less like a second-class citizen compared to Visual Studio.
Snippets and Surround With. I've long been a big proponent of using templating to boost coding productivity. And while that makes perfect sense to most application developers, I think templating is under-appreciated by many database developers. And, frankly, one reason for that might be the fact that while Visual Studio has had robust support for snippets for years now, SSMS has never had it (short of third-party add-ins).
To that end, it's nice to see that Microsoft has finally addressed this issue by providing snippets support in SSMS Denali—something that I'm expecting many DBAs and developers will find very useful.
Even so, I do find it strange that current CTPs of SSMS Denali don't yet provide any support for snippet shortcuts. (There's actually a spot where you can specify shortcut definitions, but I can't get SSMS to "honor" or use those snippets at present.) So, what that means is that instead of being able to type something like csf+TAB+TAB to use a shortcut (csf) and then "expand" that shortcut with a snippet of code with syntax to Create a Scalar Function, I actually have to either use the Edit, IntelliSense tool menu, or use Ctrl+K, Ctrl+X and then specify a few arrow keys to select the snippets I want.
Granted, that's not very hard to commit to muscle memory, and it's a small price to pay for finally having official snippet support within SSMS. (And on the off chance that you don't know how snippets work, they're just "snippets" of code that get inserted into your work environment— and which have optional support for "place-holders" that you can "tab" through and replace as a way to quickly stub-in common coding structures within your code.)
Figure 2: SSMS Denali's support for code snippets
SSMS Denali also provides support for "Surround With" snippets, which let you "wrap" a selection of code with templatized code as well, such that you can easily "wrap" existing T-SQL in a transaction, error handling, or drop it in as the "guts" of a sproc, and so on.
Even better, of course, is the fact that you can customize, create, and import your own scripts, by playing around with the Code Snippets Manager (which you can find in the Tools menu). Editing code snippets is something that many Visual Studio developers are quite used to. However, if you're going to create or edit your own snippets, I'd highly recommend using Snippet Designer as it's free and provides a much simpler and easier way of working with Visual Studio (and SSMS Denali) snippets.
I was worried that snippets wouldn't work with one of my favorite SQL Server features: T-SQL Templates. However, it didn't take me long to set up some tests that verify that you still can inject T-SQL
T-SQL Gets a Throw Statement
Every once in a while I catch myself wondering (for a fraction of a fraction of a second) if T-SQL's RAISERROR statement really does only have one "E" in the middle—or if I'm just remembering it that way.
Fortunately, with SQL Server Denali, I may be able to rid my mind of that question a bit more frequently and take advantage of something that also feels a lot more comfortable to my .NET tendencies anyhow—meaning that T-SQL is finally getting a THROW statement.
Strangely enough, this new "feature" isn't documented or mentioned at all in the "What's New?" section of Books Online, which is weird as I think it's a nice improvement.
Syntax for this new statement is pretty simple, as outlined in Books Online, and shown by the following example:
DECLARE @fakeVariable int
IF @fakeVariable IS NULL
THROW 50001, 'Such and Such can''t be NULL.', 16;
The only real caveats to note with this new statement are that the error numbers you can use have to be greater than 50,000 (because anything below 50,000 is reserved for SQL Server) and that you have to use a semicolon to terminate this statement, as shown above. It's also important to note that Books Online points out (in the "Differences between RAISE and THROW" in the documentation for the THROW statement) that THROW always "throws" errors with a severity of 16. So if you need to create fatal errors or other more advanced operations, you'll want to use RAISERROR instead.
It's also worth mentioning (for .NET developers) that you can also use a "naked" THROW; statement— as long as you're inside of a CATCH block—in which case THROW will just re-throw the current error or exception, which allows you to keep error details intact after doing any needed handling or processing within your own code.
Books Online Goes… Online
Something that may not seem like a huge win at first blush is the fact that Books Online is now going… well, online—meaning that there's no longer a specialized "help" application that you use when you need to interact with documentation. You'll still be able to "install" help locally on your machine if that's important to you (like it is for me, but what you're doing is just downloading .cab files that then get hosted locally in your browser at http://127.0.0.1 instead of at those nasty ms-help:// urls.
Consequently, I'm hoping that once this feature goes live, it will mesh more cleanly with the online documentation currently available up on TechNet and MSDN (specifically, I'm hoping that you'll just need to change hostnames/ports to get to this content, though I foresee there being problems with that working as ideally as I'd like).
Still, just being able to finally middle-click on links in Books Online (without having to go out to microsoft.com) is going to be a huge benefit. That, and in installing these help files locally, I actually found that the installation experience was much cleaner and easier than what I'm used to when it comes to installing MSDN documentation in the past. (Though Books Online has always been really easy to install—meaning that it looks like Microsoft is adopting the new "online" approach for other developer documentation as well.)
One thing that I do think I'll potentially miss though is the index. Because with the new help, there isn't a way to switch to the documentation index and type in, say, "DBCC" and get a list of all entries within Books online that start with the text "DBCC". Instead, you have to execute a search. At present I'm trying to be open-minded about this new approach as it is much more lightweight and much faster. But, if you type in "sys.dm_" looking for a list of different DMVs and other goodies, you'll be disappointed if you were a heavy user of the index feature in previous versions of Books Online.
Using the WITH RESULTS clause to re-project Sproc output
So far this article has just looked at environmental/UI changes, but that doesn't mean there aren't some new, cool technical features and improvements as well. For example, one new T-SQL feature that will resonate with many developers is a new ability to re-project the output of stored procedures (or, more specifically, the results of the EXECUTE statement).
Effectively, what this means is that Denali makes it possible to redefine the names and data types of columns being output by sprocs—without having to modify the sproc itself.
So, for example, in the following example, a sproc is created with "lame" output names—and there are actually two SELECT statements executed—meaning that there will be two distinct result sets returned by this sproc when run.
CREATE PROC dbo.GetLameOutPutNames
@input int,
@flag bit
AS SELECT
@input AS intInput,
@flag AS bitFlagForSomething
-- Assume this sproc has 2 results sets:
SELECT GETDATE() AS server_date_time
RETURN 0
GO
EXEC dbo.GetLameOutPutNames 22, 1
WITH RESULT SETS
(
-- first statement/result-set:
(
InputValue int,
[Flagged] char(1)
)
, -- second result-set (note the comma)
(
ExecutionTime smalldatetime
)
);
However, by using the WITH RESULT SETS clause, I'm able to define two results sets (one per each result set returned by the sproc) and redefine not only the names for the columns being output, but the data types as well.
Sadly, you can't do much else (e.g., it would be cool to be able to throw in a CASE statement on the [Flagged] field and translate it into "Y" / "N" output) other than rename and retype the output. Likewise, note that you must use a semicolon with this clause.
My guess is that this feature won't get used a ton by rank and file developers (though I'm curious to see if it ends up having any side benefits for ORMs, specifically for the Entity Framework). It will, however, end up being fairly useful in cases where developers might need to address versioning considerations when using sprocs—as the new WITH RESULTS clause will make it easy for consumers of stored procedures to easily remap or redefine the output of existing stored procedures without modifying them. (In other words, the WITH RESULTS clause provides a painless way for sproc consumers to throw in a new layer of abstraction—something that's very helpful both for creating a distinction between physical and logical schema and in versioning needs.)
The problem, however, is that as cool as this functionality is, it's limited in the sense that if your stored procedure outputs three columns, the WITH RESULTS clause MUST define three columns on the way out—which means that you couldn't so something like concatenate/combine FirstName + ' ' + LastName AS FullName or anything similar. Consequently, this limitation does hamper the scope of this new feature in some tangible ways.
But, it looks like there's quite a bit planned for this feature based upon what I'm seeing in Books Online—especially in terms of the AS OBJECT and AS FOR XML clauses (though I've yet to sort out that syntax into a working sample yet). Likewise, the RESULT SETS NONE option is also very curious and easy to test, though I can't think of good examples of when using it would make sense.
Either way you slice it though, this feature can and will come in handy in a variety of scenarios—and my guess/hope is that it will only improve as future versions of SQL Server are released.
Stay Tuned for More Features
In my next installment on this topic we'll take a look at some of the new and enhanced metadata capabilities that SQL Server Denali exposes to developers, and we'll also look at SEQUENCES, FILESTREAM, and FULLTEXT Indexing changes, along with something that many developers will be excited about: built-in pagination support.
Michael K. Campbell ([email protected]) is a contributing editor for SQL Server Magazine and a consultant with years of SQL Server DBA and developer experience. He enjoys consulting, development, and creating free videos for www.sqlservervideos.com.
