The only SQL session I am taking this week took place yesterday and was all about T-SQL futures by Michael Wang. To my delight it was all about SQL 2008, so I got information that I will be able to use fairly soon.
Michael detailed the following SQL 2008 areas relevant to T-SQL:
- DataTime Data types
- SQL Language
- Procedural programming
DataTime Data Types
Data types start with the splitting out of date and time as individual types. This allows a recording of only date information and only time information. The DATE datatype now provides for a historic day prior to 1753 as well as a larger precision. The new DATE data type is 60% smaller than the existing DATETIME. The TIME datatype has a precision of 100ns, but that can be changed to save storage space. A new DATETIME2 datatype was created with greater precision and the idea being it will break legacy datetime applications dependent on the current precision. Last is the DATETIMEOFFSET: this is a time zone aware value that includes date and time and offset. It directly matches a new .NET type in the 3.0 framework.
New functions are also available to support the new date & time data types. Some examples of these are SYSTEMDATATIMEOFFSET and TODATETIMEOFFSTE, as well as extensions into the existing DATEPART and DATENAME functions.
SQL Language
Under the SQL language area, we now get a MERGE statement. This is a new SQL 2006 compliant syntax that can do an insert, update and delete all within the same statement. This is useful in OLTP databases to merge from an external source. It is useful in data warehouses to do an incremental update of fact tables. Note the OUTPUT clause of this statement will show you exactly what it did.
Grouping sets are another part of the language improvements. It allows you to define multiple groups in the same query. You could do this before with the duplicate query and UNION ALL statement, but the new syntax will allow for significantly improved performance.
Table value constants are now supported as well. This looked similar to how the generics are used today in C# to initialize values.
The TABLE datatype was also created to support the tables value as well as for allowing table information to be passed as a parameter (yay!).
One last thing in this area Michael called SQL Delighters: we finally get compound assignments – +=, -=, etc
Procedural Programming
Object dependency start this off. You can now see who references what the a new system stored procedures. For example, see everything that references the table in the database. Note this functionality currently does not cross the database boundary.
Here are a new hierarchical data types with built in functions to support them. This functionality is currently provided by common table expressions, that the new data types and functions will give you better performance.
Michael also talked briefly about sparse columns. These allowed for optimized spacing by using filtered indices. You define indices over subsets of data.
Summary
I had always viewed SQL 2008 to be primarily a DBA-oriented, rather than developer-oriented release. Nice to see we still get some things to play with. I look forward to working with the new features and can see immediate applications for several of my clients. While the T-SQL updates alone are probably not enough of a reason to update to SQL 2008, when taken with other DBA features I am definately reccomending it to my clients.
