In part one of this review of new features for developers in SQL Server Denali, we looked at improvements to SQL Server Management Studio, Books Online, T-SQL's new THROW statement, and the WITH RESULTS clause for the EXECUTE statement. In this second part we'll look at how minor changes to FILESTREAM functionality, Full-Text Indexing, and interactions with metadata will impact developers—and we'll also take a look at T-SQL's new support for SEQUENCEs and pagination.
Non-Developer–Focused Changes That Will Impact Developers
In addition to some (as yet undocumented) new special stored procedures that enable additional FILESTREAM management, one of the great things that SQL Server Denali brings to the table is the ability to add multiple data containers per each FILESTREAM filegroup—something that will help significantly with scalability and manageability for DBAs. I'm anticipating that similar manageability benefits will also arise from Denali's support for newly added FileTables as well.
And while new improvements that make FILESTREAM management easier for DBAs may not sound like something developers should care about, anything that results in less pushback from DBAs is a big win for developers. Stated differently, one of the nice things about Denali is that it's doing a great job of continuing to integrate key features and capabilities introduced by SQL Server 2005 by making them less unwieldy and easier to manage.
Similar things can be said about Full-Text indexing, which used to be a bit of a pain for DBAs to manage due to the additional overhead required for file placement and backups. But with SQL Server 2005 and then 2008, these manageability concerns were removed as the SQL Server team migrated Full-Text indexing support transparently into the database engine itself—enabling easier adoption.
Only, with Denali, we finally see some new, albeit minor, changes to Full-Text indexing capabilities themselves—by means of allowing for easier distinction between the attributes and contents of files that are Full-text indexed, and by means of a custom proximity term that allows developers (and users) to specify how "NEAR"-related search terms are allowed to be for a desired match.
Again, neither of these changes to FILESTREAM or Full-Text Indexing is heavily geared toward developers, but they are strong indicators that Denali continues to refine previous features and better integrate them directly into SQL Server, making them safer for developers to leverage and use when solving problems.
Another subtle way in which SQL Server Denali will benefit developers is through increased, or enhanced, support for working with metadata. Or, more specifically, for querying SQL Server about what kinds of results will be projected or returned from specific queries or operations.
Today it's possible to determine what kind of output you can expect when executing a single query or firing off a stored procedure using the SET FMTONLY option to ON (something that I always just hate seeing in Profiler Traces when doing performance tuning—because it means something is out there getting ready to fire off dynamically created SQL that will (hopefully) later be parameterized).Only, working with FMTONLY is insanely painful—because all it does is force SQL Server to return exact samples or projected outputs WITHOUT any data. So, a simple SELECT * FROM SomeTable will output all columns, but no rows. Similar results can be achieved when executing stored procedures, and so on
Happily, however, Denali will change all of that by providing a host of new special stored procedures that can be used to easily interrogate the kinds of results that SQL Server will project—even when multiple result sets are being returned, as shown in Figure 1.
Figure 1: Existing metadata interactions via FMTONLY vs. new metadata features in Denali
In my estimation, many developers will find this new metadata functionality "neat" but not really something that they're interested in. If, however, you are interested in it, then I'd recommend taking a look at Aaron Betrand's blog post on Metadata enhancements, as he's done a great job of covering the specifics of these new enhancements (both in terms of existing pain points and upcoming improvements). Still, I do see this functionality being beneficial to developers working on their own ORMs or who have hand-rolled data access layers in larger environments. (I also can't help but wonder if the addition of this functionality doesn't help, in some way, the Entity Framework team—though they've done fine up until now without these capabilities.) I also think this functionality will be huge for DBAs and other scripters who want to work with, and store, the output of DMVs and other system objects.
In terms of actual, new, T-SQL features that developers will be able to take advantage of, one great new addition that Denali provides is for something known as a SEQUENCE (which Oracle has had for nearly forever). Simply stated, the best way to describe a SEQUENCE would be to think of it as being a bit like an IDENTITY "object"—meaning that it is seeded and increments as defined by the object creator, but it doesn't need to be a column or part of a table. Instead, it's a "free-roaming" object that manages incrementation, or sequencing, needs.To work with a SEQUENCE, you first create (or define) it with a seed and increment. Then you can query against it using the NEXT VALUE FOR syntax—which can be used in a stand-alone SELECT statement or as part of an INSERT as shown in Figure 2.
Figure 2: Simple example of a SEQUENCE
Obviously, using a SEQUENCE is more work than merely using an IDENTITY property hard-coded into a single table. But it provides much greater flexibility—by specifically decoupling sequencing or incrementation needs from the table itself.
One place I see SEQUENCEs being helpful will be when modeling inheritance within some specialized schemas—allowing increased cohesion among similar (or inherited) record types stored in different tables.
One other place that I see the addition of a SEQUENCE object having a huge impact on databases is where it comes to an over-abuse of the use of unique identifier, or GUIDs, by developers. This happens in cases where they want to serialize existing, logical, relationships into the database intact—meaning that they don't want to have to serialize the parent, get an incremented ID, and then serialize any child records against that ID. Scenarios like this pan out well when using GUIDs, as a new GUID can be pulled out of thin air, used as the "ParentId" of the parent and any children that need to be serialized into the database, and then INSERTed as needed. The problem, of course, is that GUIDs don't make for very good primary keys and can lead to some ugly issues with fragmentation that can negatively impact performance.
As such, I'm planning on an article and sample application that will showcase how to circumvent this coding paradigm through the use of sequences, meaning that developers will be able to roughly continue as they do at present without needing to incur the expense and overhead of uniqueidentifier data types when not truly needed. Until then, the key thing to note is that SEQUENCES may not be necessary or even needed in all situations. But they are a great new addition that will provide some increased flexibility—something that Itzak Ben-Gan has addressed in his Blog and in SQL Server Magazine.
Built-in Support for Pagination
One valid complaint that developers used to working with MySQL have always been able to level at SQL Server is the fact that MySQL has built-in support for pagination using the LIMIT clause, where something like this is possible:
-- MySQL example (won't work in SQL Server):
SELECT name, phone FROM Contacts LIMIT 3,10
With Denali we finally get server-side pagination support, as an extension of the ORDER BY clause (which makes perfect sense as you'll want/need an ORDER BY clause to define how data is ordered on the way out if you care about pagination).
Overall, I find the syntax a bit too verbose, but there are also some more advanced options that you can take advantage of when working with this functionality that extend above and beyond the simple paging result I've demonstrated in Figure 3.
Figure 3: SQL Server finally gets some (albeit verbose) syntax for server-side pagination
I'm also confident that this new pagination functionality will be abused in some environments in terms of performance when working with huge result sets. I also foresee some real ugliness when this new feature of the ORDER BY clause meets common developer "tricks" for pulling back the TOP 100 PERCENT from certain, sorted, views and so on. Similarly, I see a whole new crop of questions in forums about locking problems stemming from server-side pagination as well—as this will likely become a problem when working on larger systems where there's lots of data and concurrency.
That said, that doesn't mean that this won't be a great new addition to SQL Server, nor that this functionality wasn't needed. It will be a great addition for developers in the vast majority of use cases.
A Smorgasbord of Developer Benefits
Of course, any time a new version of SQL Server is released, there are also typically a small number of existing (usually older) features and capabilities that are deprecated or removed from service. In that regard, Denali is no different, as it, too, deprecates and removes a handful of features.
The biggest thing to note in terms of backward compatibility is that users of SQL Server 2000 won't be able to upgrade directly to SQL Server Denali once it ships—they will be too many versions (i.e., the wrong compatibility level) out of date to be officially supported for upgrade purposes (meaning that they won't be able to back up SQL Server 2000 databases and restore them on SQL Server vNext when it ships). Instead they'll have to upgrade to SQL Server 2005 or 2008 first—which, I think, effectively means that they'll likely never upgrade.
Otherwise, I'm still holding my breath for a cleaner and easier way to format dates within SQL Server than using CONVERT (I'd like to see something in SQL Server that's akin to how you can arbitrarily format dates to strings in .NET with .ToString("yyyy-MM-dd") and so on.)
Still, SQL Server Denali does provide some great new features that will benefit developers directly—even if this release has been a bit more focused on Business Intelligence.
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.