New Data Types in SQL Server 2008

Executive Summary: From new spatial data types such as GEOGRAPHY to new date data types such as DATETIMEOFFSET, SQL Server 2008 offers DBAs an easier way to solve problems and extend SQL Server to become an enterprise-class database platform. Three listings and inline code provide help with using these new data types.

SQL Server 2008 data types push into areas beyond simple relational data. These new data types give you more control over your database design, make it easier to migrate data from other systems, and can enhance application performance.

FILESTREAM
Without a doubt, the new data type I like best is the FILESTREAM data type. Designed to solve the problem of unstructured large object (LOB) storage, it combines the performance of file system streaming APIs with the transactional integrity of the relational database. With this data type, the unstructured data is stored in the NTFS file system, and the SQL Server engine manages the link between the FILESTREAM columns and the files in the file system—including backing up and restoring file system data. Unlike with the older IMAGE and VARBINARY(MAX) data types, the SQL Server buffer pool isn’t taken up with the retrieval of the LOB data.

Before working with the new FILESTREAM data type you need to enable it using the sp_filestream_configure stored procedure:

sp_filestream_configure @enable_level = 3

Next, because SQL Server uses a special type of filegroup for FILESTREAM access you must use the new CONTAINS FILESTREAM clause, which Listing 1 shows, when creating a database that will use the FILESTREAM objects.

After you create the database, you can create tables using the FILESTREAM data type. A table using a FILE-STREAM data type requires a column with a UNIQUEIDENTIFIER. Listing 2 shows how to declare and use the new FILESTREAM data type.

DATE and TIME
How long have we waited for these? Although DATETIME offers what you need, frequently you just want the date or the time. Plus, it was often a problem to convert data from other systems that used discreet date and time column values to and from the older DATETIME data type.

The new DATE data type is a native SQL Server data type that’s ANSI compliant. It uses the format YYYY-MM-DD and can contain values from 0001-01-01 to 9999-12-31.

Another new native SQL data type, the TIME data type, complements the DATE data type. TIME uses the format hh:mm:ss[.nnnnnnn] and can contain values from 00:00:00.0000000 to 23:59.59.9999999. To use it, enter the following:

CREATE TABLE dbo.MyDateTime

(MyDate DATE,
  
  MyTime TIME(3));

INSERT into dbo.MyDateTime

VALUES(‘12/25/07’, ‘22:18:48.123’)  

DATETIME2 and DATETIMEOFFSET
While DATE and TIME were designed to address simple needs for date and time storage, DATETIME2 and DATETIMEOFFSET are at the other end of the date/time continuum. Designed to address the need for more precise date/ time storage and accurate up to 100 nanoseconds, DATETIME2 uses the format YYYY-MM-DD hh:mm:ss[.nnnnnnn]. It can store values ranging from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59.59.9999999.

DATETIMEOFFSET is like DATETIME2 except that it’s also time-zone aware. It uses the format YYYY-MM-DD hh:mm:ss[.nnnnnnn] and can store values ranging from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59.59.9999999. Listing 3 shows how to use it.

HIERARCHYID
Designed to address the problem of tracking tree structures such as organization charts, the new HIERARCHYID is a variable-length system data type that stores data optimized for either depth or breadth. To help you work with it, Microsoft has included several methods to query and modify the hierarchical data including GetRoot(), GetLevel(), GetAncestor, and IsDescendant.

The code below shows how to declare a new HIERARCHYID data type. It uses the GetLevel method to create a breath-first ordering:

CREATE TABLE dbo.MyEmps

(MyEmpID HIERARCHYID,
    
  MyEmpLevel as MyEmpID.GetLevel(),
  
  MyEmpName VARCHAR(50));  

GEOGRAPHY and GEOMETRY
The integration of mapping capabilities into many applications makes the SQL Server 2008 spatial data types a welcome addition. GEOGRAPHY is a .NET-based spatial data type and uses a geodetic (round earth) model. It stores points, lines, polygons, and collections of latitude and longitude coordinates.

Another .NET-based data type, GEOMETRY uses a planar (flat earth) model, unlike GEOGRAPHY, which is primarily designed for navigation and mapping. GEOMETRY complies with Open Geospatial Consortium standards for the representation of geographic features.

To declare and assign values to GEOGRAPHY and GEOMETRY, use Listing 4.

Extending Your Reach
The new data types introduced in SQL Server 2008 solve some old problems. They also extend SQL Server 2008 beyond the realm of a traditional relational database server into an enterprise database platform.