SQL Server 2008 (formerly codenamed Katmai) will soon be officially released. This version of SQL Server focuses on providing increased management capabilities (such as the Declarative Management Framework--DMF) for DBAs, and on creating improvements and added functionality for Analysis Services users. But happily a number of great additions should excite developers as well and I’ll walk through a few of them in this article.
A New Member of the T-SQL Family–The MERGE Statement
One revolutionary addition is the new MERGE statement. On par with other core T-SQL CRUD features such as INSERT, SELECT, UPDATE, and DELETE, the MERGE statement is an ISO-2003 compliant command that is primarily intended to handle what many database users refer to as “UPSERT” functionality. For example, say you have an application where you either need to log a new entry for something that hasn’t been added to your system, or update it if it’s already been added previously. Without the MERGE statement you must either run a SELECT statement to see if a row has already been logged and then UPDATE or INSERT if it’s not there. Or you can try to UPDATE first, and then INSERT if the UPDATE doesn’t affect any rows. With the MERGE statement you can do this all in one fell swoop. Here’s a rather extensive example of the syntax, pulled from the SQL Server November CTP:
MERGE Production.ProductInventory AS pi USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail sod JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID AND soh.OrderDate = GETDATE() GROUP BY ProductID) AS src (ProductID, OrderQty) ON (pi.ProductID = src.ProductID) WHEN MATCHED AND pi.Quantity - src.OrderQty <> 0 THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty WHEN MATCHED AND pi.Quantity - src.OrderQty = 0 THEN DELETE;
Documentation from a previous CTP’s Books Online provides an overview, but the syntax has changed a bit between CTP releases. However, if you study that statement listed above, you can see that it’s just a built-in switch (or case) that specifies what to check for, and then what to do if the data is found or not. What’s cool about this statement, though, is that you can perform the entire check-in a single operation – which drastically increases performance during large-scale UPSERT operations or when you’re trying to merge two large tables.
Table-Valued Parameters
Ever wanted – or more importantly – needed to pass in a table or array of values to your stored procedures? Well, now you can with Table-Valued Parameters. Imagine the following:
CREATE TYPE myTable AS TABLE ( key varchar(6), value varchar(20) ) GO
CREATE PROC dbo.InsertKeyValuesFromWebApp @tvp myTable READONLY, @owner int AS SET NOCOUNT ON
INSERT INTO [dbo].[SomeTable] (key, value, owner) SELECT key, value, @owner FROM @tvp
RETURN 0 GO
For simpler applications, this functionality might seem like overkill, but for many complex applications, having this code on hand will be a lifesaver. This is something I’ve wanted for years (and which I’ve had to get around by sending in XML fragments as varchar parameters and then re-hydrating on the server) , and now we’ll have native support for it – I can’t wait. I can also see this becoming a wicked bit of functionality when combined with LINQ down the road.
Date, Time, DateTime2, and DateTimeOffSet Data Types
While it’s not the most horrible thing in the universe, storing date-only or time-only data in SQL Server today requires the use of bizarre conventions combined with the use of magic-numbers. For example, a datetime with a date of 1900-01-01 means “no date,” and a datetime with a time of 00:00:00.000 means “no time” is recorded. With SQL Server 2008 we’ll finally get date and time data types that will make date-only and time-only domains much more efficient and simple to create and leverage. Early SQL Server 2005 betas introduced date-only and time-only data types, but they were dropped during later betas due to testing requirements and didn’t make it into the final release.
Developers will also appreciate the fact that the new time data type, as well as the new datetime2 data type (I wish they had called it a longdatetime), don’t use rounding for fractional seconds (.000,.003, or .007) like the current datetime data type does. These longer data-types also enjoy significantly longer fractional capacities (9999999 instead of just .999) making them more suitable for use in a wider number of applications. There’s also a new datetimeoffset data type, which is a datetime2 data-type that’s intrinsically aware of time offsets.
The HierarchyID
The new hierarchyid is an interesting new data-type that lets developers represent the position of an entity within a hierarchy, such as an org-chart or reporting structure. SQL Server 2005’s introduction of Common Table Expressions (CTEs) helped make recursive queries (typically needed to resolve hierarchies in the past) much easier to manage. But having an actual hierarchyid data type will make it that much easier. What’s cool, or interesting, about this data type though, is that it’s an intrinsic CLR data-type – meaning that Microsoft is starting to leverage Common Language Runtime functionality natively. I wonder what other CLR additions we’ll see in the future.
IntelliSense
One SQL Server 2005’s biggest disappointments for me was that the initial promise of T-SQL IntelliSense wasn’t realized when SQL Server 2005 released. (Ironically, Microsoft switched from the SQL Workbench approach to a Visual Studio approach prior to release; we all know that IntelliSense is one of the best things about Visual Studio, so it’s almost silly that SQL Server Management Studio doesn’t provide IntelliSense.) Happily, SQL Server 2008 provides IntelliSense. It’s not perfect, or as fully-featured as I’d like it to be (I want it to know my column and parameter names), but it’s a step in the right direction. Having the code-collapse functionality is just awesome. (I want to shut down a rumor I’ve heard that the degree of IntelliSense you get might depend on which version of SQL Server you’re using. So you might not get as much IntelliSense with SQL Server 2008 Express as you would with Enterprise. In talks with Microsoft editors at WindowsDevPro have learned that the IntelliSense will not vary with the version of SQL Server you buy.)
Other Fun Features
Other cool features that developers and architects will love are sparse columns, GROUPING SETS, large user-defined data-types (that can exceed the 8000-byte limit), and the new FILESTREAM data type (which we’ll likely look at in a subsequent newsletter) – to say nothing of the improvements that I’m also excited about as a DBA. To get your own look at what’s new in SQL Server 2008, you can visit the (outdated) Books Online that are currently on MSDN2, or you can download and play with the bits yourself by pulling down the November CTP.
