SQL Server 2012: Top New Features for .NET Developers

Like its predecessors SQL Server 2008 and SQL Server 2008 R2, SQL Server 2012 boasts many features and improvements targeted at business intelligence (BI) and operations personnel (i.e., DBAs). But that doesn't mean that SQL Server 2012 ignores developers. In fact, SQL Server 2012 includes a number of great improvements and features in the form of new tooling, coding and programmability enhancements, and even new improvements to the underlying SQL Server engine that developers can take advantage of. Let's run down the list of new features that will benefit .NET developers who use SQL Server. For more information, see "Free SQL Server Resources for Developers" and "SQL Server 2012 Brings DBAs and Developers Together."

New SQL Server 2012 Tooling

SQL Server 2012 boasts some exceptional tooling improvements in the form of a significant upgrade to SQL Server Management Studio (SSMS) and a replacement of Business Intelligence Development Studio (BIDS), resulting in developer-related additions to Visual Studio 2010 and the release of SQL Server Data Tools (SSDT). At the heart of all of these tooling changes, however, is the fact that SQL Server 2012 has been "upgraded" such that SSMS and all associated developer tooling now runs atop the Visual Studio 2010 engine.

SQL Server Management Studio 2012. SSMS 2012 packs a number of powerful productivity benefits that developers who use SSMS for development tasks will end up loving. The most obvious change to SSMS is that it's built on Visual Studio 2010, meaning that it picks up a new, darker theme that more closely matches the Visual Studio theme, as shown in Figure 1.

Being built atop Visual Studio 2010 also means that SSMS 2012 picks up Visual Studio 2010's vastly improved multi-monitor support -- something that I've long awaited (because my desktop sports three monitors). Consequently, my favorite new feature of SSMS is the ability to "tear out" tabs from the main work area and drag them into different monitors or outside of the main IDE. Doing so lets me easily compare scripts or context in one file with the contents of another file without having to toggle back and forth between tabs (and without having to spin up a different instance of SSMS, as I was wont to do previously). Likewise, the ability to "detach" tool windows and position them in other monitors is another huge productivity benefit.

Another subtle productivity benefit that SSMS 2012 picks up is that it finally inherits Visual Studio's Clipboard buffer. This means that you can cycle through previously copied text by holding down the Shift key while pressing Ctrl+V.

An even more powerful productivity benefit that SSMS 2012 sports is the ability to leverage code snippets from within SSMS. This capability is something that has been sorely lacking for those of us addicted to snippets from developer experience within Visual Studio over the plast half-decade. Strangely, though, rather than being keyed off of "mnemonics" as is the case in Visual Studio, SSMS snippets are (by default) injected by means of pulling up a snippets "menu" that can be accessed via the Edit, IntelliSense, Insert Snippet menu option -- or via a similar Surround With menu option. Both of these menu options, of course, can be accessed via hotkeys (Ctrl+K, X and Ctrl+K, S), and the existing library of snippets available is quite large and helpful out of the box.

Initially I was a bit shocked that these snippets don't use SQL Server's age-old template functionality, but it didn't take me long to realize that the parameter-replacement semantics provided by these snippets (in the form of tabbing from one parameter to the next) provide a much better user experience than "old-school" templates. Moreover, although the out-of-the-box snippets that come with SSMS 2012 are great, it doesn't take much energy or effort to customize or create your own snippets (especially if you're using an awesome tool like Snippet Designer).

Furthermore, when it comes to customizing and managing snippets, the Tools, Code Snippets Manager option provides paths to existing snippets along with the option to add or remove snippets as needed. All that Code Snippets Manager is missing is the option to manage shortcuts (or mnemonics); shortcuts are supported, but only if you crack files open manually. Still, SSMS 2012's snippets feature is a significant productivity boon for developers.

As expected, SSMS 2012 continues to deliver the native support for working with SQL Azure databases that debuted with SSMS 2008 R2, although (maybe this is just my imagination) working with SQL Azure databases in SSMS 2012 feels just a tiny bit more responsive than before. Either way, SSMS 2012 sports a few new tasks for managing data-tier applications when you right-click on an Azure database -- so that's a minor improvement as well.

SQL Server 2012 improvements for Visual Studio 2010. As mentioned previously, another significant tooling change for developers is that with SQL Server 2012, BIDS ceases to exist. Of course, BIDS was really never anything more than a lightly "skinned" version of Visual Studio that provided specialized project templates and underlying tooling support for SQL Server Reporting Services (SSRS), SQL Server Integration Services (SSIS), and SQL Server Analysis Services (SSAS). In my experience, BIDS was always a source of unending frustration for developers who wanted to do .NET development and manage any form of BI (or SS*S) projects as well -- simply because of all of the versioning problems that continued to plague BIDS throughout its history.

Consequently, one of the things I was most excited about with SQL Server 2012 was that it retired BIDS. This retirement means that it's now possible with SQL Server 2012 to directly create and manage all BI-related projects directly within Visual Studio 2010. It's also worth mentioning that these project types pick up some new UI benefits and tweaks. For example, one of my favorite changes with SQL Server 2012 is that SSIS projects now have a full-blown tab in the .dtsx tab for parameters, as shown in Figure 2. This tab replaces the formerly employed approach, where naturally horizontal editing of parameters was crammed into a Tool Window that was vertical by default.

Although it's nice to finally be able to painlessly open and work with BI projects in Visual Studio 2010, the bad news is that age-old compatibility problems still exist in the sense that if you want support for managing BI-related projects in Visual Studio 2010, you have to uninstall Visual Studio (if it's already installed) and then reinstall it after you install SQL Server 2012 -- which, of course, is beyond lame. That said, an easier way to get BI-related project templates is to just install SSDT, but that currently comes with some drawbacks as well.

SQL Server Data Tools 2012. According to Microsoft, SSDT was effectively designed to replace the SQL Server "Data Dude" SKU (aka Visual Studio Team System 2008 Database Edition) of yore. As such, SSDT provides a number of designers and editing capabilities for working with so-called database projects, where SSDT can help track changes made via designers, and so on. Correspondingly, SSDT also includes schema-comparison tooling and capabilities. A thorough overview of all the features and benefits provided by SSDT is outside the scope of this article. If you're interested in learning more about SSDT, you can find great information and insights about what it does and how it works by visiting the SSDT home page or by visiting the SSDT Team Blog.

Although I've spent a bit of time working with SSDT to "put it through the paces," I must confess that I've never been very excited about Data Dude (not just because of the name), and that lack of enthusiasm now extends to SSDT as well. Stated simply, as a longtime database developer and former production DBA, I'm just not afraid of the complexity associated with database change management, and I can't help but find that SSDT is inadequate for handling changes in the mission-critical environments of my SQL Server consulting clients. Therefore, given that SSDT (somehow) breaks the ability to close documents in SSMS 2012 by middle-clicking, I'd personally take the ability to middle-click my documents closed over all the features and benefits that SSDT provides. Of course, that bug is hopefully only a temporary setback, and I'm sure other developers and DBAs would feel much differently about SSDT. Consequently, SSDT is well worth a further look, because it's a major new feature of SQL Server 2012.

Programmability Enhancements: T-SQL and Engine Improvements

Even as SQL Server 2012's improved tooling and enhanced IDEs provide big productivity wins for developers, SQL Server 2012 also sports a number of programmability enhancements and functions that SQL Server developers will enjoy. SQL Server 2012 packs in a number of great features and capabilities that developers have been requesting for a while now, along with some great surprises that provide some useful new capabilities. Let's take a look at the new programmability features.

T-SQL gets a THROW statement. For SQL Server developers who are familiar with the .NET Framework, the lack of a T-SQL THROW statement has always been a bit strange. Happily, SQL Server 2012 has addressed that lack -- though you'll want to pay attention to the Remarks section of SQL Server Books Online ( BOL) for this new feature because there are a number of limitations as to how this statement can be used. Likewise, as cool as this new feature is, just remember that RAISERROR (despite its archaic spelling) still provides a number of powerful features that THROW (and PRINT) simply don't offer.

T-SQL finally supports built-in pagination. I still remember how disappointed I was more than a decade ago to learn that SQL Server didn't provide built-in support for pagination as MySQL did. Happily though, SQL Server 2012 finally introduces full-blown, first-class support within T-SQL for pagination, as shown in Figure 3. Note, too, that pagination is technically a feature or argument of the ORDER BY clause -- and that Microsoft actually recommends using OFFSET and FETCH as a replacement for TOP.

-- Create and populate a simple table-- full of 'contact' information:CREATE TABLE dbo.Contacts (ContactId int IDENTITY(1,1) NOT NULL,FirstName varchar(60),LastName varchar(60),Phone varchar(60));INSERT INTO dbo.ContactsSELECT name, name, nameFROM master.dbo.spt_values;-- Skip 300 rows 'into' the results and-- take the next 10 records:SELECT ContactId, FirstName, LastName, PhoneFROM dbo.ContactsORDER BY ContactIdOFFSET 300 ROWSFETCH NEXT 10 ROWS ONLY;

Support for sequences. Another great new feature that SQL Server 2012 provides (which Oracle has had for nearly forever) is support for sequences. A sequence can best be described as being a bit like an IDENTITY "object" because it behaves just like an IDENTITY column without actually being a column. Developers can create sequences, query them for one or more IDs (which are returned in sequence), and then do whatever they want with those supplied IDs prior to INSERTing them into a table that needs a unique, sequentially incrementing, identity value, as shown in Figure 4.

-- Create a simple sequence:CREATE SEQUENCE dbo.ExampleSequence AS intSTART WITH 1 INCREMENT BY 1;-- Create a simple/test table, too:CREATE TABLE dbo.SequentialTable (SampleId int NOT NULL,SampleValue nvarchar(40) NOT NULL);-- Sample/example of easiest way to grab value:SELECT NEXT VALUE FOR dbo.ExampleSequence;-- Now copy 'next' value (2) into a parameter:DECLARE @NextSequence intSELECT @NextSequence = NEXT VALUEFOR dbo.ExampleSequence;-- And use it for an INSERT.-- But in non-trivial examples - you could use it for FK inserts-- or other operations as well BEFORE trying the following INSERT.INSERT INTO dbo.SequentialTable (SampleId, SampleValue)VALUES (@NextSequence, '@NextSequence will have a value of 2.');SELECT * FROM dbo.SequentialTable;GO

One of the big benefits of sequences is that developers can use them in much the same way as they use GUIDs today, but without the type of performance issues associated with the ugly fragmentation that ensues when using unique identifiers as a clustered index key. In addition to that benefit, sequences open up other very cool possibilities by virtue of some of their extended capabilities, such as the ability to CYCLE or "repeat" sequence values after they hit a specified maximum identity value. SQL Server 2012 also provides a number of options for intelligently working with and querying sequence metadata.

Metadata discovery improvements. Speaking of metadata, SQL Server 2012 also provides drastically improved support for metadata discovery, which makes it much easier for developers to determine the shape of projected output from queries, sprocs, views, and other objects that can be queried. Although many developers will never use this functionality, I can't help but think of how useful it will be for developers creating micro ORMs (which I suspect will become much more popular as developers tire of the bloat and overhead associated with Entity Framework).

Projection redirection and the WITH RESULT SETS argument. Another great new feature of SQL Server 2012 that developers will find useful is the ability to "re-project" or change the output of stored procedures by specifying new column names, casts, and other sorts of operations when executing stored procedures by means of the WITH RESULT SETS clause (see Figure 5). This feature is something that will be very valuable for helping to assist with versioning concerns and considerations in larger and more complex applications and deployments.

-- An INSANELY simple sample sproc:CREATE PROC dbo.TestProc@input1 int,@input2 varchar(20)ASSET NOCOUNT ONSELECT @input1 AS [Output1], @input2 [Output2]RETURN 0GO-- Now transform the output/projection/results:EXEC dbo.TestProc 292, 'This is Some Text'WITH RESULT SETS ( ([Column 1] int, [Column 2] varchar(20)) );

Programmability Enhancements: New T-SQL Functions

As outlined in BOL, SQL Server 2012 introduces a bevy of new functions that developers can use. Here are my thoughts about a few of them.

FORMAT(). In my mind, this single function is one of the hands-down best new features of SQL Server 2012, simply because the functionality that it provides has been so sorely needed for so long. And for .NET developers, the immediate and obvious benefits of this new function should be readily apparent just by looking at Figure 6. Another thing that I like about the new FORMAT() function is that it represents an additional influx of CLR functionality directly into T-SQL -- something that I hope to see more of in the future.

SELECTFORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO Formatted Date],FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS [Full ISO],FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [Long-hand Date (EN)],FORMAT(GETDATE(), 'MMMM dd, yyyy', 'fr-FR') AS [French Date],FORMAT(22.7, 'C', 'en-US') AS [US Currency],FORMAT(22.7, 'C', 'en-GB') AS [UK Currency],FORMAT(99 * 2.226, '000.000') AS [Padded Decimal],FORMAT(12345678, '0,0') AS [Finally: Commas in Large Numbers];

CHOOSE(). With the CHOOSE() function, you can quickly and easily translate an integer value into an array of corresponding string values, as shown in Figure 7.

-- CHOOSE() makes these easy to 'format' in ad hoc-- reports. The following returns 'Male' or position-- number 2 in the 1-based (i.e., non-0 based) array.SELECT CHOOSE(2, 'Female','Male', 'Unknown')AS [Gender];-- Just be aware that hard-coding values into-- 'permanent' code can/will cause problems long term.-- The following returns NULL - as 4 exceeds array or is not found.SELECT CHOOSE(4,'Female','Male','Unknown') AS [x];

Personally, I think that the new CHOOSE() function is a mixed blessing. On the one hand, this function can and will make minor "formatting" and reporting needs much easier to handle. On the other hand, it doesn't take much thought at all to see how the use of CHOOSE() within code can and will lead to "magic numbers" programming within code and how it will lead to versioning problems and issues that will ultimately cause this function to generate all sorts of problems over the long term.

Conversion functions and date and time functions. For developers familiar with the .NET Framework, T-SQL's new TRY_CONVERT() and TRY_PARSE() functions will be a welcome new addition, along with a host of new Date and Time functions (of varying benefit and usefulness). All told, SQL Server 2012 does a great job of introducing an assortment of new functions that will make programing T-SQL that much easier.

Engine Enhancements That Benefit Developers

Although it's a common or even logical assumption that enhancements to the core SQL Server engine are things that typically excite DBAs, that doesn't mean that many of these benefits can't translate into tangible improvements that will aid developers. As such, SQL Server 2012 actually ships with a number of programmability enhancements that it lists in the "What's New" section of BOL with descriptions about new developments for the SQL Server Engine. These enhancements include some great new improvements to full-text indexing, enhanced spatial features, and the addition of FILETABLE functionality that extends existing FILESTREAM storage capabilities.

But, above and beyond these obvious engine enhancement benefits, there are also a couple of other big benefits that aren't as obvious to spot: partially contained databases and a new version of SQL Server Express.

Contained databases. With SQL Server 2012, contained databases provide a means for effectively decoupling SQL Server databases (and their users, collations, and other assets) from the underlying SQL Server instance itself. Stated differently, contained databases are much more "portable" in the sense that they can be easily moved from one server to another -- without worries about orphaned users and other issues that have typically made moving databases problematic. Thus, not only are contained databases a big win in corporate environments where DBAs need to shunt databases around for load-balancing purposes, they're also a huge win for ISVs (and developers) who want to more easily copy, move, or deploy databases with their software. Similarly, for web developers who rely on SQL Server as a back-end database, contained databases help make developers less dependent on hosting platforms as their databases become more portable -- another huge win.

Better yet, since there remains a fine line between what kind of code and operations are server-dependent and what kinds of programming and operations can be isolated into contained databases, Microsoft has done a fantastic job of making it easy to query specialized dynamic management views (DMVs) that will let developers (and DBAs) know how "contained" or portable their databases are. As such, my anticipation is that contained databases are not only one of the biggest wins for developers in SQL Server 2012 but also a key component in eventually helping Microsoft push SQL Azure as a database-hosting platform.

SQL Server 2012 Express LocalDB. Another great feature that SQL Server 2012 offers for developers is a new lightweight installation of SQL Server Express. This new version of SQL Express, SQL Server 2012 Express LocalDB, makes ISV and other forms of "embedded" SQL Server deployments much easier to tackle by means of allowing a much more streamlined installation process along with the ability to let SQL Server run in-process with applications. Best of all, documentation for this new developer-friendly version of SQL Server is included in BOL -- and the price remains, of course, free.

Discontinued and Deprecated Features

Of course, with every new release of SQL Server, there are also a few features and bits of syntax that are either removed outright or are slated for later removal. Happily, with SQL Server 2012, the list of deprecated and removed features is relatively small. This, in turn, means that SQL Server 2012 does a great job of providing developers with new features -- with very little worry or concern for negative problems of backward compatibility.