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.