Exception Handling in Yukon Transactions

Ok, it is Friday, so an easy one to digest, but quite powerful. Yukon has made a major improvement in Error Handling inside of TSQL transactions. You can now catch Transaction Abort Errors using the TRY / CATCH model without any loss of the transaction context. This makes me very happy. The syntax is here:

BEGIN TRY

sql statement

END TRY

BEGIN CATCH TRAN_ABORT

    sql statement

END CATCH

See my article on SQLJunkies.com.

Yukon Book

Friday, December 19, 2003 1:29:41 PM (Eastern Standard Time, UTC-05:00)
Comments [16]  |  Trackback

New Data Types in Yukon

There are a few very cool new types. The most noticeable are categorized as: DateTime types, varchar(max), and XML. There are a few datetime datatypes, but the most interesting new DateTime datatypes are the UtcDateTime, Date and Time types The UtcDateTime datatype is time zone aware and is used in scenarios where the server and clients span multiple time zones. It uses an expanded range (from 1 AD for 9999 AD) and precision (100 nanoseconds.) The Date datatype is useful when you just want to store the simple date and has the same expanded range. The Time is a new datatype for Time only and has expanded precision (100 nanoseconds.) Varchar(Max) is an extension to varchar, nvarchar, varbinary that will store data up to 2GB and is an alternative to text/ntext/image. It uses the Uses MAX size specifier, like so:

CREATE TABLE TablewithMaxColumn

(Customer_Id int, CustomerLifeStrory varbinary(max))

All string functions operate on varchar(max), you can use the TSQL SUBSTRING to read chunks and the UPDATE statement has been enhanced to support update of CHUNKS.

To accommodate the growing importance of XML Yukon includes an XML datatype. In the past developers would store XML in a varchar or text/ntext field. That was not optimal since the XML was not in its native format and you could not query its contents efficiently. The new datatype allows developers to store XML in its native format (well actually it is stored in a BLOB field) and gives you the ability to via XQuery to query parts of the XML field.

The XML datatype is a full blown TSQL citizen, able to have table columns and participate in a CAST or CONVERT statement. You can even use the FOR XML statement to convert tabular data into XML from a SQL query:

DECLARE @xmlData AS XML

SET @xmlData = (SELECT * From Customers FOR XML AUTO, TYPE)

SELECT @xmlData

As expected the results of a row look like this:

<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545"/>

You can also insert this XML data into a new XML field in a table and have data validations based on XSD be enforced by Yukon. Pretty cool stuff.

See my article on SQLJunkies.com.

Yukon Book

Thursday, December 18, 2003 3:14:36 PM (Eastern Standard Time, UTC-05:00)
Comments [16]  |  Trackback

Common Table Expressions in Yukon

A Common Table Expression closely resembles a non-persistent view. A CTE is a temporary named result set that you define in your query that will be used by the FROM clause of the query. Each CTE is only defined once (but can be referred to as many time as it is in scope) and lives for as long as the query lives. CTEs can also be used to perform recursive operations. Here is the syntax to create a CTE:

With <name of your CTE>(<column names>)

As

(

<actual query>

)

Select * from <name of your CTE>

An example of a simple CTE is shown here:

With LondonCustomersCTE

As

(Select * From Customers Where City='London')

Select * From LondonCustomersCTE

Here is an example where we get a list of all the count of Customers’ orders in the orders table as a CTE and then a simple inner join with the Customer’s table to return the full name of the customer. (Sure this can be done without a CTE, but think about the ease of aggregating in the CTE only, it makes your code much easier to deal with.)

The code is here:

With CustomerOrderCountCTE(CustomerID, OrderCount)

As

(

     select CustomerID, Count(*)

     from orders

     group by CustomerID

)

Select CU.CompanyName, City, COC.OrderCount

From Customers As CU Inner Join CustomerOrderCountCTE As COC

     on CU.CustomerID = COC.CustomerID

Order By COC.OrderCount Desc

The true power of CTEs are when you use then recursively. A recursive CTE is constructed from a minimum of two queries, the first or Anchor Member (AM) is a non-recursive query and the send is the recursive query or the Recursive Member (RM). The AM and RM are separated by a UNION ALL statement. Here is the syntax:

With SimpleRecursive( field names)

As

(

     <Select Statement for Anchor Member>

     Union All

     <Select Statement for the Recursive Memember>

)

Select * From SimpleRecursive

See my article on SQLJunkies.com.

Yukon Book

Wednesday, December 17, 2003 12:25:22 PM (Eastern Standard Time, UTC-05:00)
Comments [4]  |  Trackback

TOP Enhancements in Yukon

In previous versions of SQL Server TOP allowed you to limit the number of rows returned as a number or percentage in Select Queries. I use this all the time. It has gotten more flexible in Yukon where TOP can be used in Delete, Update and Insert queries. What is even cooler is that Yukon will allow you to specify the number of rows (or percent) by using variables or subqueries. I love the ability to dynamically set the TOP, so much that I have started writing Stored Procedures that accept a NumberofRows parameter like so: 

Create Procedure usp_SEL_ReturnTopOrders

@NumberofRows int

As

Select TOP (@NumberofRows) OrderID

From Orders

Order By OrderID

Executing the stored procedure is easy, just pass in how many records that you want (in this case it is 100):

usp_SEL_ReturnTopOrders 100

Using a subquery can be very powerful when you are doing things on the fly. A real simple example to demonstrate the concept is show here, we are getting the TOP n customers based on how many records we have in our Employees table:

Select TOP (Select Count(*) from Employees) *

From Orders

See my article on SQLJunkies.com.

Yukon Book

Tuesday, December 16, 2003 4:31:23 PM (Eastern Standard Time, UTC-05:00)
Comments [8]  |  Trackback

PIVOT Data in Yukon (RIP Case and Rozenshtein)

Let’s face it, users usually want to see data in tabular format, which is a bit of a challenge when we usually store data in a highly relational form. PIVOT is a new TSQL operator that you can specify in your FROM clause to rotate rows into columns and create a traditional “Crosstab” query without using CASE, Rozenshtein or subqueries. You have always been able to do this easily in Access with Pivot and Transform, but SQL Server was always a step behind.

Using PIVOT is easy. First in your select statement you need to specify the values you want to pivot on, in the following example, we will use the Year of the Order. Our FROM clause looks normal except for the PIVOT part. The PIVOT statement creates the value we plan on showing in the rows of the newly created columns, in this case I am using the aggregate SUM of the TotalAmount (a calculated field in our FROM clause). Then we have to use the FOR operator to list the values we are going to Pivot on in the OrdersYear column.  The example is shown here:

SELECT CustomerID, [1996] AS 'Y1996', [1997] AS 'Y1997', [1998] AS 'Y1998', [1999] as 'Y1999'

FROM

     (SELECT CustomerID,OD.UnitPrice * OD.Quantity - OD.Discount AS TotalAmt,

          Year(dbo.Orders.OrderDate) as OrdersYear

          FROM dbo.Orders INNER JOIN dbo.[Order Details] As OD

              ON dbo.Orders.OrderID = OD.OrderID)

          As Orders

     PIVOT

     (

          SUM(TotalAmt)

          FOR OrdersYear IN([1996], [1997], [1998], [1999])

     ) AS XTabData

Order BY CustomerID

The results look like this:

That is all there is to it. Of course this is a real simple example to show you the new concept, you can then of course get more sophisticated aggregates and even use Common Table Expressions in the FROM clause. Also you can use the UNPIVOT operator to normalize data that is already pivoted.

See my article on SQLJunkies.com.

Yukon Book

Monday, December 15, 2003 4:44:32 PM (Eastern Standard Time, UTC-05:00)
Comments [3]  |  Trackback

A Dutch Houseboat

So yesterday I was on a true houseboat in the Netherlands. Apparently my pal Remi grew up on a houseboat and we went to his childhood home to drop off something to his parents after he picked me up at the airport. It was very cool and you could really feel the house rock. What a treat.

Non-Techie

Friday, December 12, 2003 9:54:47 AM (Eastern Standard Time, UTC-05:00)
Comments [0]  |  Trackback

An Access Killer?

The reporting engine in Access is almost 10 years old with major modification. It is that good. Now something better has come around. SQL Server Reporting Services, due out any week now, could be an Access killer. After demoing it to the London Database Forum last night, we all realized that it is pretty cool. An Access Report Killer, yes it is (but Access will still be better for local disconnected reporting). Attention Access developers, SSRS is based off the old Access report designed, so you have a leg up, so go learn it. If you don’t know about SQL Server Reporting Services, go download it from Microsoft now!

And Microsoft: Its about time!

SQL Server

Thursday, December 11, 2003 9:29:01 AM (Eastern Standard Time, UTC-05:00)
Comments [11]  |  Trackback

A killer Infield

Japanese star Kazuo Matsui decided to join the New York Mets, accepting the Amazins’ three-year offer today. Kazuo has been called a "faster, stronger version of Inchiro.” Now the Mets will have two killer shortstops. My guess is the Jose Reyes will go to second. With Matsui and Reyes, the Mets now have two switch-hitters at the top of their lineup who have speed, power, high OBPs, high BAs and great defensive skills. Playoff bound? Not yet. A major improvement, sure.

Now the true question: Is New York City ready for two Matsui’s??

Non-Techie

Monday, December 08, 2003 6:27:17 PM (Eastern Standard Time, UTC-05:00)
Comments [12]  |  Trackback