CTP 3 for SQL Server Denali just barely dropped with a whole new set new features, functions, and benefits that developers are going to love. In fact, some of these new features are great enough that developers will wonder why it has taken SQL Server so long to provide them.
SQL Server Denali CTP 3: New Features and Goodies
In "SQL Server Denali for Developers" (part 1 and part 2), I covered new features and additions to SQL Server Denali that developers will be able to leverage. However, as we draw closer to an actual release date for the next version of SQL Server, it's great to see that Microsoft is still dropping in new features and capabilities that developers will be able to take advantage of.
As such, CTP 3 adds a whole bevy of not only new features and capabilities, but better documentation and guidance about how these features and capabilities can be used. To get a feel for what's new anytime a new release of SQL Server is unleashed upon the world, I always take a peek at the "What's New?" section of SQL Server Books Online. Therefore, if you're curious about SQL Server Denali CTP 3 and haven't had time to install it yet, I'd recommend taking a look at the top-level overview of what's new— to see what's new all across the board in Denali. Likewise, if you're just interested in the Database Engine (as opposed to other services and features), there's a dedicated "What's New" section there as well—which showcases improvements and changes to availability, manageability, scalability, and security enhancements—along, of course, with new programmability enhancements.
SQL Server Finally Gets a FORMAT() Method
In regard to what is easily my favorite new addition to SQL Server in years, I was happy to see that rumors of SQL Server finally getting a FORMAT() method were correct.
Just speaking in terms of dates and times, there have been far too many times in the past where I needed some form of custom date-time or timestamp output that required some sort of perversion resembling parsing and concatenating parts or aspects of two different CONVERT() formats.
However, with the new FORMAT() method, those days will be over (when working against Denali databases). And, rather than providing a lengthy description of how the FORMAT() method works, I'll just include a screenshot (Figure 1) that aptly showcases how a picture can be worth a thousand words:
Figure 1: Denali FORMAT method
And what's great about this screenshot is that while it showcases how easy it is to format dates or times for other cultures or how easy it is to use place-holders for complete control over output formatting, it's only focused on working with dates and times—meaning that it can format more than just dates and times. (Though, truth is that if the FORMAT() only worked with dates and times, I'd still be ecstatic about it.)
Instead, though, as you can see from this screenshot (Figure 2), the FORMAT() method can handle all sorts of types of other formatting requirements:
Figure 2: Using the FORMAT method to format various types of output
In this case, not only have I formatted date-time output as desired, but I was also easily able to format currency, decimals, add padding, and—another huge win in my book—add commas into a larger number by simply specifying FORMAT(input, '0,0').
As Books Online points out, the core functionality for the FORMAT() function is provided by the Common Language Runtime (CLR), meaning that you need to have it installed on your SQL Server to run correctly (and it's pretty rare that you wouldn't do this). Even better, you don't have to have the CLR enabled in databases where you want this to work—as it's a system function—so you'll always get access to it as long as the CLR is installed.
What this means, though, is that since FORMAT() is really just wrapping .NET functionality, then .NET developers can easily take advantage of familiar formatting paradigms outlined for serializing (or outputting) simple .NET objects, as defined on MSDN. So, if you're a .NET developer, then the new FORMAT() functionality will feel like it's long been overdue.
CONCAT(), IIF(), and CHOOSE() Functions
It's also worth noting too that Denali CTP 3 also exposes a new CONCAT() function, which should make string concatenation a bit easier in many cases. More specifically, I'm hoping that in cases where developers need to intelligently mingle row output with static text, that we'll see more use of CONCT() in the future, to eliminate some of the CAST/CONVERT and ISNULL(NULIF()) hairballs that have made their way into code over the years as a way to deal with inline evaluations for potentially null or missing data.
And, speaking of inline evaluations for output data, it's nice to note that there are now two new logical operators, including an IIF()and CHOOSE() function. IIF() is effectively an "immediate if" (or I like to think of it more as an inline if) that I'm not quite sure I'm sold on just yet—mostly because I'm a snob and can't help but associate IIF() with VBA, VB6, VB.NET, and Macros. But, taken together with the CHOOSE() function—which will make it easy to handle "static" lookups in simple cases (as shown in the screenshot below in Figure 3), and I'm hoping that we'll start to see T-SQL code that's increasingly easier to read and maintain when it comes to the simple kinds of output formatting needed by many queries.
Figure 3: SQL Server Denali CHOOSE function
PARSE(), TRY_PARSE(), and TRY_CONVERT()
For .NET developers, SQL Server Denali's addition of PARSE(), TRY_PARSE(), and TRY_CONVERT() will seem like familiar territory. The key thing to note about these features and functions, though, is that they take optional arguments in the form of languages or cultures—which can actually now be set with a new SET LANGUAGE feature or which can be passed in as optional arguments.
Otherwise, the great thing about these new functions is that the TRY_ variants of them work just as you'd expect, meaning that they'll pass out NULL values if the conversion fails instead of throwing an error or exception.
(Interestingly, though, while I was going to show an example of how to use TRY_CONVERT, I simply couldn't get it to run—getting, instead, the error: 'TRY_CONVERT' is not a recognized built-in function name. So, apparently some parts of the documentation for CTP 3 are a bit more advanced than the actual bits that shipped with the CTP.)
EOMONTH() and a Host of other DateTime Functions
Finally, there's also a bevy of new Date and Time Functions, most of which are focused on being able to "pull in" dates or times into SQL Server from "parts" of dates or times being passed in by variables or other forms of input. As such, there are various functions with names like DATEFROMPARTS(), TIMEFROMPARTS(), and various variations on similarly named options that result in different data types, and so on.
But hidden among these functions is a neat new function called the EOMONTH() function—short for End Of MONTH—which takes in a single date-time as an input, then returns the last day of the month for the input specified, as Figure 4 below shows:
Figure 4: SQL Server Denali's EOMONTH function
Granted, pulling this off previously wasn't impossible. Typically it involved the process of going to the first day of the month following the input in question and doing a DATEADD() to subtract a single day. It didn't require anything truly hideous, but, as you can see from the example above, this new approach is obviously much easier.
Thoughts on the CLR
Another thing that I've found immensely beneficial while doing some initial reconnaissance of Denali CTP 3 is that it does a fantastic job of shifting between .NET 2.0 and .NET 4.0 assemblies when playing with CLR functionality. This was a bit of a worry for me, as SQL Server 2005 and 2008 provided CLR functionality but shipped against .NET 2.0; whereas Denali will have 2.0, 3.5, and 4.0 to contend with. However, in my initial experimentation, I was able to painlessly flit back and forth between using 4.0 and 2.0 assemblies with absolutely no problems. So I'm hoping that bodes well for the "interoperability story," as it seems there are always some compatibility and other problems between the latest version of SQL Server and the latest version of Visual Studio and the .NET Framework.
Otherwise, the addition of these new functions is, in my estimation, a big set of improvements for some of the more tedious things that developers commonly tackled—so I'm definitely glad to see their inclusion. I'll end up using the FORMAT() statement alone for a host of different things – including outputting date-times and adding commas into larger sets of numbers.
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.
In "SQL Server Denali for Developers" (part 1 and part 2), I covered new features and additions to SQL Server Denali that developers will be able to leverage. However, as we draw closer to an actual release date for the next version of SQL Server, it's great to see that Microsoft is still dropping in new features and capabilities that developers will be able to take advantage of.
In regard to what is easily my favorite new addition to SQL Server in years, I was happy to see that rumors of SQL Server finally getting a FORMAT() method were correct.
Just speaking in terms of dates and times, there have been far too many times in the past where I needed some form of custom date-time or timestamp output that required some sort of perversion resembling parsing and concatenating parts or aspects of two different CONVERT() formats.
However, with the new FORMAT() method, those days will be over (when working against Denali databases). And, rather than providing a lengthy description of how the FORMAT() method works, I'll just include a screenshot (Figure 1) that aptly showcases how a picture can be worth a thousand words:
Figure 1: Denali FORMAT method
And what's great about this screenshot is that while it showcases how easy it is to format dates or times for other cultures or how easy it is to use place-holders for complete control over output formatting, it's only focused on working with dates and times—meaning that it can format more than just dates and times. (Though, truth is that if the FORMAT() only worked with dates and times, I'd still be ecstatic about it.)
Instead, though, as you can see from this screenshot (Figure 2), the FORMAT() method can handle all sorts of types of other formatting requirements:
Figure 2: Using the FORMAT method to format various types of output
In this case, not only have I formatted date-time output as desired, but I was also easily able to format currency, decimals, add padding, and—another huge win in my book—add commas into a larger number by simply specifying FORMAT(input, '0,0').
As Books Online points out, the core functionality for the FORMAT() function is provided by the Common Language Runtime (CLR), meaning that you need to have it installed on your SQL Server to run correctly (and it's pretty rare that you wouldn't do this). Even better, you don't have to have the CLR enabled in databases where you want this to work—as it's a system function—so you'll always get access to it as long as the CLR is installed.
What this means, though, is that since FORMAT() is really just wrapping .NET functionality, then .NET developers can easily take advantage of familiar formatting paradigms outlined for serializing (or outputting) simple .NET objects, as defined on MSDN. So, if you're a .NET developer, then the new FORMAT() functionality will feel like it's long been overdue.
CONCAT(), IIF(), and CHOOSE() Functions
It's also worth noting too that Denali CTP 3 also exposes a new CONCAT() function, which should make string concatenation a bit easier in many cases. More specifically, I'm hoping that in cases where developers need to intelligently mingle row output with static text, that we'll see more use of CONCT() in the future, to eliminate some of the CAST/CONVERT and ISNULL(NULIF()) hairballs that have made their way into code over the years as a way to deal with inline evaluations for potentially null or missing data.
And, speaking of inline evaluations for output data, it's nice to note that there are now two new logical operators, including an IIF()and CHOOSE() function. IIF() is effectively an "immediate if" (or I like to think of it more as an inline if) that I'm not quite sure I'm sold on just yet—mostly because I'm a snob and can't help but associate IIF() with VBA, VB6, VB.NET, and Macros. But, taken together with the CHOOSE() function—which will make it easy to handle "static" lookups in simple cases (as shown in the screenshot below in Figure 3), and I'm hoping that we'll start to see T-SQL code that's increasingly easier to read and maintain when it comes to the simple kinds of output formatting needed by many queries.
Figure 3: SQL Server Denali CHOOSE function
PARSE(), TRY_PARSE(), and TRY_CONVERT()
For .NET developers, SQL Server Denali's addition of PARSE(), TRY_PARSE(), and TRY_CONVERT() will seem like familiar territory. The key thing to note about these features and functions, though, is that they take optional arguments in the form of languages or cultures—which can actually now be set with a new SET LANGUAGE feature or which can be passed in as optional arguments.
Otherwise, the great thing about these new functions is that the TRY_ variants of them work just as you'd expect, meaning that they'll pass out NULL values if the conversion fails instead of throwing an error or exception.
(Interestingly, though, while I was going to show an example of how to use TRY_CONVERT, I simply couldn't get it to run—getting, instead, the error: 'TRY_CONVERT' is not a recognized built-in function name. So, apparently some parts of the documentation for CTP 3 are a bit more advanced than the actual bits that shipped with the CTP.)
EOMONTH() and a Host of other DateTime Functions
Finally, there's also a bevy of new Date and Time Functions, most of which are focused on being able to "pull in" dates or times into SQL Server from "parts" of dates or times being passed in by variables or other forms of input. As such, there are various functions with names like DATEFROMPARTS(), TIMEFROMPARTS(), and various variations on similarly named options that result in different data types, and so on.
But hidden among these functions is a neat new function called the EOMONTH() function—short for End Of MONTH—which takes in a single date-time as an input, then returns the last day of the month for the input specified, as Figure 4 below shows:
Figure 4: SQL Server Denali's EOMONTH function
Granted, pulling this off previously wasn't impossible. Typically it involved the process of going to the first day of the month following the input in question and doing a DATEADD() to subtract a single day. It didn't require anything truly hideous, but, as you can see from the example above, this new approach is obviously much easier.
Thoughts on the CLR
Another thing that I've found immensely beneficial while doing some initial reconnaissance of Denali CTP 3 is that it does a fantastic job of shifting between .NET 2.0 and .NET 4.0 assemblies when playing with CLR functionality. This was a bit of a worry for me, as SQL Server 2005 and 2008 provided CLR functionality but shipped against .NET 2.0; whereas Denali will have 2.0, 3.5, and 4.0 to contend with. However, in my initial experimentation, I was able to painlessly flit back and forth between using 4.0 and 2.0 assemblies with absolutely no problems. So I'm hoping that bodes well for the "interoperability story," as it seems there are always some compatibility and other problems between the latest version of SQL Server and the latest version of Visual Studio and the .NET Framework.
Otherwise, the addition of these new functions is, in my estimation, a big set of improvements for some of the more tedious things that developers commonly tackled—so I'm definitely glad to see their inclusion. I'll end up using the FORMAT() statement alone for a host of different things – including outputting date-times and adding commas into larger sets of numbers.
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.
Figure 3: SQL Server Denali CHOOSE function
For .NET developers, SQL Server Denali's addition of PARSE(), TRY_PARSE(), and TRY_CONVERT() will seem like familiar territory. The key thing to note about these features and functions, though, is that they take optional arguments in the form of languages or cultures—which can actually now be set with a new SET LANGUAGE feature or which can be passed in as optional arguments.
Otherwise, the great thing about these new functions is that the TRY_ variants of them work just as you'd expect, meaning that they'll pass out NULL values if the conversion fails instead of throwing an error or exception.
(Interestingly, though, while I was going to show an example of how to use TRY_CONVERT, I simply couldn't get it to run—getting, instead, the error: 'TRY_CONVERT' is not a recognized built-in function name. So, apparently some parts of the documentation for CTP 3 are a bit more advanced than the actual bits that shipped with the CTP.)
EOMONTH() and a Host of other DateTime Functions
Finally, there's also a bevy of new Date and Time Functions, most of which are focused on being able to "pull in" dates or times into SQL Server from "parts" of dates or times being passed in by variables or other forms of input. As such, there are various functions with names like DATEFROMPARTS(), TIMEFROMPARTS(), and various variations on similarly named options that result in different data types, and so on.
But hidden among these functions is a neat new function called the EOMONTH() function—short for End Of MONTH—which takes in a single date-time as an input, then returns the last day of the month for the input specified, as Figure 4 below shows:
Figure 4: SQL Server Denali's EOMONTH function
Granted, pulling this off previously wasn't impossible. Typically it involved the process of going to the first day of the month following the input in question and doing a DATEADD() to subtract a single day. It didn't require anything truly hideous, but, as you can see from the example above, this new approach is obviously much easier.
Thoughts on the CLR
Another thing that I've found immensely beneficial while doing some initial reconnaissance of Denali CTP 3 is that it does a fantastic job of shifting between .NET 2.0 and .NET 4.0 assemblies when playing with CLR functionality. This was a bit of a worry for me, as SQL Server 2005 and 2008 provided CLR functionality but shipped against .NET 2.0; whereas Denali will have 2.0, 3.5, and 4.0 to contend with. However, in my initial experimentation, I was able to painlessly flit back and forth between using 4.0 and 2.0 assemblies with absolutely no problems. So I'm hoping that bodes well for the "interoperability story," as it seems there are always some compatibility and other problems between the latest version of SQL Server and the latest version of Visual Studio and the .NET Framework.
Otherwise, the addition of these new functions is, in my estimation, a big set of improvements for some of the more tedious things that developers commonly tackled—so I'm definitely glad to see their inclusion. I'll end up using the FORMAT() statement alone for a host of different things – including outputting date-times and adding commas into larger sets of numbers.
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.
Figure 4: SQL Server Denali's EOMONTH function
Another thing that I've found immensely beneficial while doing some initial reconnaissance of Denali CTP 3 is that it does a fantastic job of shifting between .NET 2.0 and .NET 4.0 assemblies when playing with CLR functionality. This was a bit of a worry for me, as SQL Server 2005 and 2008 provided CLR functionality but shipped against .NET 2.0; whereas Denali will have 2.0, 3.5, and 4.0 to contend with. However, in my initial experimentation, I was able to painlessly flit back and forth between using 4.0 and 2.0 assemblies with absolutely no problems. So I'm hoping that bodes well for the "interoperability story," as it seems there are always some compatibility and other problems between the latest version of SQL Server and the latest version of Visual Studio and the .NET Framework.
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.
