Exploring the T-SQL Enhancements in SQL Server 2005 : New Data Types

10/13/2010 9:34:21 AM
QL Server 2005 has several cool new data types. The most useful are varchar(max) and xml.

varchar(max) Data Type

varchar(max), nvarchar(max), and varbinary(max) are extensions of the varchar, nvarchar, and varbinary data types that can store up to 2 gigabytes (GB) of data. They are alternatives to text, ntext, and image and use the MAX size specifier. Using one of these data types is easy—you just specify it in your CREATE TABLEmax) identifier, as shown here: statement with a (

CREATE TABLE TablewithMaxColumn
(Customer_Id int, CustomerLifeStory varbinary(max))

All the standard T-SQL string functions operate on varchar(max), including concatenation functions SUBSTRING, LEN, and CONVERT. For example, you can use the T-SQL SUBSTRING function to read parts of the string (chunks), and the UPDATE statement has also been enhanced to support the updating of chunks.

This is a vast improvement over the limitations in SQL Server 2000, where TEXT and IMAGE fields are used to store this type of data. These data types are not allowed as stored procedure variables and cannot be updated directly, and many of the string manipulation functions don’t work on the TEXT data type.

xml Data Type

To accommodate the growing importance of XML, SQL Server 2005 includes an xml data type. In the past, developers stored XML in a varchar or text/ntext field. That wasn’t optimal because the XML wasn’t in its native format and you couldn’t query its contents efficiently. The new data type allows you to store XML in its native format (actually, in a BLOB field) and gives you the ability, using XQuery, to query parts of the XML field.

The xml data type is a full-blown T-SQL citizen—it can be used as a type for table columns and variables, and it can participate in a CAST or CONVERT statement. You can even use the FOR XML statement to convert tabular data into XML from an SQL query, as shown here:

Listing 1. Converting data into XML
USE AdventureWorks
DECLARE @xmlData AS xml
SET @xmlData = (SELECT * FROM HumanResources.Employee FOR XML AUTO, TYPE)
SELECT @xmlData

As expected, the results of a row look like this:

<HumanResources.Employee EmployeeID="1" NationalIDNumber="14417807"
ContactID="1209" LoginID="adventure-works\guy1" DepartmentID="7"
ManagerID="16" ShiftID="1" Title="Production Technician - WC60"
EmergencyContactID="1498" AddressID="61" BirthDate="1972-05-
15T00:00:00" MaritalStatus="M" Gender="M" HireDate="1996-07-
31T00:00:00" SalariedFlag="0" BaseRate="12.4500" PayFrequency="1"
VacationHours="21" SickLeaveHours="30" CurrentFlag="1"
rowguid="AAE1D04A-C237-4974-B4D5-935247737718" ModifiedDate="1996-07-
24T00:00:00" />
[more elements]
PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
Top 10 Video Game
-   Company of Heroes 2: The British Forces [PC] Trailer
-   SDCC 2015: Plants vs. Zombies Garden Warfare 2 | Seeds of Time Map Gameplay Reveal
-   Microsoft HoloLens: Partner Spotlight with Case Western Reserve University
-   Cossacks 3 [PC] Musketeer Animations Trailer
-   Call Of Duty: Black Ops III [PS4/XOne/PC] Zombies - Shadows of Evil Trailer
-   No Time To Explain [XOne/PC] Multiplayer Trailer
-   Bierzerkers [PC] Early Access Trailer
-   Downward [PC] Kickstarter Trailer
-   Grip [PS4/PC] Trailer
-   Hitman [PS4/XOne/PC] Debut Trailer
-   Gears of War: Ultimate Edition [XOne] Recreating the Cinematics Trailer
-   Gravity Falls: Legend of the Gnome Gemulets [3DS] Debut Trailer
-   Street Fighter V [PS4/PC] Ken Trailer
-   Doctor Who | Series 9 Teaser Trailer
-   Transformers: Devastation | Gameplay Trailer (SDCC 2015)
Game of War | Kate Upton Commercial