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
GO
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]
