Error Handling
Another long-awaited T-SQL enhancement involves error management. Yukon introduces a TRY/CATCH construct that lets you implement error handling similar to that in other development environments. You can now trap errors that would terminate the connection in previous releases of SQL Server and deal with them in an elegant, organized way. As an example, create the T1 table:
CREATE TABLE T1(col1 int NOT NULL PRIMARY KEY)
The code in Listing 4, page 32, shows how you can use TRY/CATCH to trap errors such as primary key violations or conversion errors when you're inserting data into the table.
The TRY construct works for transaction-abort errors only, meaning that you have to set the XACT_ABORT option to ON and write your code within a transaction. In the TRY block, you include the transaction for which you want to trap errors. On an error, the transaction enters a "failed" state. You remain in the transaction's context—SQL Server holds the locks and doesn't reverse the transaction's work. Control passes to the nearest CATCH block, where you can examine the error and take corrective measures after rolling back the failed transaction.
When you run Listing 4's code the first time, the CATCH block isn't activated because no error occurs. When you run the code the second time, the CATCH block traps a primary key violation. But see what happens when you comment out the first INSERT statement and uncomment the second, then run the code again. You've trapped a conversion error.
Data Types and the BULK Rowset Provider
Yukon changes data types in several interesting ways. For one thing, it introduces a new XML data type that lets you store and manipulate XML data in variables and in table columns. In addition, you can define dynamic columns such as varchar, nvarchar, and varbinary by using the new MAX option instead of specifying an explicit size, so you have a more natural alternative to the large object (LOB) data types text, ntext, and image. The MAX option lets you store up to 2GB of data in a column. By "more natural," I mean that with the new MAX option, you can use regular DML to manipulate these large dynamic columns instead of using the cumbersome WRITETEXT, UPDATETEXT, and READTEXT commands as you have to in previous versions to manipulate LOBs. Also, Yukon introduces a new BULK rowset provider that lets you manipulate files as rowsets easily and elegantly.
To illustrate these changes, run the code that Listing 5 shows. This code creates the TestLargeObjects table and inserts a row into it. To update the XML column x with the XML result of a query, run the following code:
UPDATE TestLargeObjects
SET x = (SELECT * FROM Customers FOR XML AUTO)
WHERE keycol = 1
To load a text file into the varchar(MAX) column vc, you use the OPENROWSET() function with the BULK provider, specifying the file path and the SINGLE_CLOB option as follows:
UPDATE TestLargeObjects
SET vc = (SELECT vc
FROM OPENROWSET(
BULK 'c:\temp\textfile1.txt',
SINGLE_CLOB) AS CLOB(vc))
WHERE keycol = 1
The SINGLE_CLOB option means that you're manipulating a single text file that will be returned as a single row with a single column. Similarly, you can load a Unicode-formatted file into an nvarchar(MAX) column by specifying the SINGLE_NCLOB option or load a binary file to a varbinary(MAX) column by specifying the SINGLE_BLOB option.
Probably the most exciting change is the separation of date and time data types. The following code creates a table with separate date and time columns and inserts a new row into it:
CREATE TABLE DateTimeTest(datecol date, timecol time)
INSERT INTO DateTimeTest
VALUES(CAST('2003-11-01' AS
date), CAST('10:30:59.999999'
AS time))
The new date and time data types are CLR-based, meaning that they were developed based on the .NET infrastructure that Yukon supports. As a result, the separate date and time data types provide many useful methods and properties. For example, applying the ConvertToString() method to both date and time lets you format the date and time values according to a specified format string, as the following query shows:
SELECT
datecol::ConvertToString(
'MM/dd/yyyy') AS thedate,
timecol::ConvertToString(
'HH:mm:ss.fff') AS thetime
FROM DateTimeTest
Figure 5 shows the result of running this query. The result column thedate contains a formatted date value in the form MM/dd/yyyy, and the result column thetime contains a formatted time in the form HH:mm:ss.fff.
The Future of T-SQL
As you can see, Yukon brings many exciting T-SQL—related enhancements and new features. With Yukon, you need to write less code to achieve the same results as in earlier releases, and you get better-performing solutions. As a set-based relational language, T-SQL has always beem strong, but now it's even stronger.