In the first two posts on CTP3 features, I discussed the new AlwaysOn and Security features in SQL Server “Denali” CTP3, and then the Business Intelligence and SSIS/Data Tools features. This post is about programmability enhancements and miscellaneous features that do not fall into other categories. As before, these are not all of the features included in Denali, only the ones that I think will be of most interest to a wide audience, or features that I am excited about for one reason or another.
Programmability
There are many programmability enhancements in Denali, but here are a few that I think are worth noting:
- SQL Server (code-named “Juneau”) – Until now, SQL Server development revolved around a mixture of Visual Studio, SSMS and third-party tools, and even then, often fell short of what database developers need on a daily basis. Microsoft has worked to address these needs with the implementation of Juneau. Juneau is intended to be the single platform for developing and deploying SQL Server code – including source control, DML and DDL, BI, SSIS, and both on-premise and SQL Azure. It includes declarative, mode-based development, the ability to perform version-based targeting, improved development experiences for SSIS developers and more. The last version I used was a private build not ready for public use, but even so it had some pretty incredible features. I think that starting with Denali, database developers will begin to shift more and more of their development to Juneau. One of my few complaints is that it does not have full support for cross-database functionality, but Microsoft is aware of that limitation and I expect to see at least some of those limitations addressed in future versions of the tool.
- PowerShell 2.0 Support – extended support through PowerShell 2.0 and SQLPS.exe allow much more power and flexibility in automating SQL Server tasks.
- Contained Databases (CDBs) – While this is not specifically a programmability improvement, it does greatly affect the software life-cycle, and therefore all database developers. The real limitation to CDBs is that they are currently designed around an application living in a single database, but the idea is that all functionality around the application (from the database perspective) will be contained within a single database (CDB). That is, all users, jobs, data, et cetera will live inside a single contained database. That CDB can then be moved from server to server without having to script and redeploy items like logins. CDBs is definitely a step in the right direction, and if you have an application that uses a single database, you should definitely look at CDBs.
- ANSI OFFSET / FETCH – This is for result set paging and can really simplify your life if you have previously had to deal with complex code just to implement paging from your application.
- Sequence Generators – with sequence generators, you can create identity-like values instead of or in addition to the identity columns. The CREATE SEQUENCE statement can be used to create global sequence generators in integer types. The integer types can be built-in or user-defined, and you can specify options such as START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE/NO CYCLE, CACHE/NO CACHE. This feature is very powerful and is not something that can be discussed in a just a few sentences, but Sequence Generators may dramatically change how you write code, so definitely look them up in BOL and play around with them!
- Improved Error Handling – Error handling finally works like a real programming language in SQL Server “Denali”. Here are a few of the summary items that make error handling dramatically better:
- Abort statement (different from severity level now!)
- Statement Abort
- Scope Abort
- Batch Abort
- Transaction Abort
- Connection Abort
- THROW/ CATCH/ re-THROW – this all actually works now and works like you would expect. The functionality is very complete and does not automatically use sys.messages. The THROW statement allows <number>, <message> and <state>.
- New Scalar Functions – these include new conversion functions like TRY_CONVERT() and TRY_CATCH(), conversions to and from strings [FORMAT(), PARSE(), TRY_PARSE()], and other miscellaneous functions [IIF(), CHOOSE(), CONCAT()]. In addition, there are new date and time functions: EOMONTH(), DATEFROMPARTS(), TIMEFROMPARTS(), DATETIMEFROMPARTS(), DATETIME2FROMPARTS(), and SMALLDATETIMEFROMPARTS()
- Spatial Data Improvements – a number of improvements have been made to spatial data functionality in Denali, including: circular arcs on the ellipsoid, support for full globe spatial objects, functional parity of geometry and geography data types, and spatial index performance improvements.
Other Features
There are several features which don’t really fit into a single category, so I have put them in the “Other Features” bucket. Some of these are great features though, so don’t think that because I left them to last that they are insignificant! Explore these new features and I think you will find that there are multiple features that may be of use to you.
- FileTable – SQL Server 2008 had the ability to use Filestream to add unstructured data to SQL Server. Filestream was a little difficult to use effectively, and required code to process your files and put them into SQL Server. In addition, it was not supported with database mirroring (DBM). FileTable completely changes the way that you can use SQL Server. With FileTable, you can expose an SMB share to Windows and simply drop files into the share and use that share exactly like you would any folder within Windows – you can create files, folders, delete them, move them, etc. In reality, when you do this you are actually modifying SQL Server and all of the files are stored in SQL Server and are searchable. On the SQL Server side, you can query or modify the file properties and attributes, change the parent folder or delete any of the files or folders. As one more added benefit, it is now fully supported by Availability Groups (AGs), so you can keep a copy of the files on your secondaries as well. This feature is everything about SQL Server and nothing about it at all. Using this feature alone, you can look at any non-SQL Server application and see a way to use FileTable and SQL Server. Simply take the application’s files, and drop them into FileTable and you instantly have full database and AG access to all of that data to query, change, move offsite, etc. While this is not typically the way that people view SQL Server, I think this feature has the potential to drastically change our file systems.
- SQL Server 2012 Express Local Database Runtime (LocalDB) – if you have ever used SQL Server Compact Edition for a lightweight database for an application, you have probably been pretty frustrated by the lack of programmability support. SQL Server 2012 LocalDB is a lightweight version of SQL Server Express, is a zero-installation version (like CE), but does support all of the programmability features of Express.
- Server Core support – SQL Server can now be installed on Windows Server Core. This might be important to you for security, licensing or other reasons. I think that many companies will be excited about the ability to install SQL Server on Windows Core.
- Partitioning – until SQL Server 2008 SP2 or R2, you could only have a maximum of 1000 partitions. For many deployments, including data warehouses, this limit was many times a difficult barrier. In SP2 and R2, SQL Server began allowing up to 15,000 partitions, but only after running sp_db_increased_partitions on each DB, and even then, it altered the DB version, possibly creating you problems if you tried to restore the database on another server. With SQL Server Denali, all of these quirks go away and the product natively supports up to 15,000 partitions.
- Distributed Replay – SQL Server has historically been very limited in its ability to capture and replay a workload for testing purposes. SQL Server Denali introduces the Distributed Replay Utility (DRU) which allows you to capture a workload and then play it back with multiple “clients”, specifying parameters for each. This is a HUGE leap forward for anyone that needs to test performance or a new system. I have used DRU quite a bit and it has been absolutely critical in our testing and deployment of SQL Server Denali.
At this point I have given a pretty good overview of all of the features. In the coming weeks I will deal with more specifics on many of these and go into much more depth about how they are used and what it means to you.
David P. Smith 