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.