SQL Server 2008 New
Data Types:
Date and Time:
Four new date and time data
types have been added, making working with time much easier than it ever has in
the past. They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
Spatial:
Two new spatial data types
have been added--GEOMETRY and GEOGRAPHY--which you can use to natively
store and manipulate location-based information, such as Global Positioning
System (GPS) data.
HIERARCHYID:
The HIERARCHYID data type is used to
enable database applications to model hierarchical tree structures, such as the
organization chart of a business.
FILESTREAM:
FILESTREAM is not a data type as such, but is a
variation of the VARBINARY(MAX) data type that allows unstructured data to be
stored in the file system instead of inside the SQL Server database. Because
this option requires a lot of involvement from both the DBA administration and
development side, I will spend more time on this topic than the rest.
Date and Time
In SQL Server 2005 and earlier,
SQL Server only offered two date and time data types: DATETIME and SMALLDATETIME. While they were useful in many
cases, they had a lot of limitations, including:
Both the date value and the time value are
part of both of these data types, and you can’t choose to store one or the
other. This often causes a lot of wasted storage (because you store data you
don’t need or want); adds unwanted complexity to many queries because the data
types often had to be converted to a different form to be useful; and often
reduces performance because WHERE clauses with these data and time data types
often had to include functions to convert them to a more useful form,
preventing these queries from using indexes.
They are not time-zone aware, which
often requires extra coding for time-aware applications.
Precision is only .333 seconds, which
is often not granular enough for some applications.
The range of supported dates is not
adequate for some applications, and the range does not match the range of .NET
CLR DATETIME data type,
which requires additional conversion code.
To overcome these problems, SQL
Server 2008 introduces four new date and time data types, which include:
DATE:
As you
can imagine, the DATE data
type only stores a date in the format of YYYY-MM-DD. It has a
range of 0001-01-01 through 9999-12-32, which should be adequate for most
business and scientific applications. The accuracy is 1 day, and it only takes
3 bytes to store the date.
TIME:
TIME is stored in the format: hh:mm:ss.nnnnnnn,
with a range of 00:00:00.0000000 through 23:59:59:9999999 and is accurate to
100 nanoseconds. Storage depends on the precision and scale selected, and runs
from 3 to 5 bytes.
DATETIME2:
DATETIME2 is very similar to the
older DATETIME data
type, but has a greater range and precision. The format is YYYY-MM-DD
hh:mm:ss:nnnnnnnm with a range of 0001-01-01 00:00:00.0000000 through
9999-12-31 23:59:59.9999999, and an accuracy of 100 nanoseconds. Storage
depends on the precision and scale selected, and runs from 6 to 8 bytes.
DATETIMEOFFSET:
DATETIMEOFFSET is similar to DATETIME2, but includes additional
information to track the time zone. The format is YYYY-MM-DD
hh:mm:ss[.nnnnnnn] [+|-]hh:mm with a range of 0001-01-01
00:00:00.0000000 through 0001-01-01 00:00:00.0000000 through 9999-12-31
23:59:59.9999999 (in UTC), and an accuracy of 100 nanoseconds. Storage depends
on the precision and scale selected, and runs from 8 to 10 bytes.
Spatial
While spatial data has been
stored in many SQL Server databases for many years (using conventional data
types) SQL Server 2008 includes the introduction of two specific spatial data
types which can make it easier for developers to integrate spatial data in
their SQL Server-based applications. In addition, by storing spatial data in
relational tables, it becomes much easier to combine spatial data with other
kinds of business data. For example, by combining spatial data (such as
longitude and latitude) with the physical address of a business, applications
can be created to map business locations on a map.
They include:
GEOMETRY:
The GEOMETRY data type
is used to store planar (flat-earth) data. It is generally used to store XY
coordinates that represent points, lines, and polygons in a two-dimensional
space. For example storing XY coordinates in the GEOMETRY data type can be used to map the exterior of a
building.
GEOGRAPHY:
The GEOGRAPHY data type
is used to store ellipsoidal (round-earth) data. It is used to store latitude
and longitude coordinates that represent points, lines, and polygons on the
earth’s surface. For example, GPS data that represents the lay of the land is
one example of data that can be stored in the GEOGRAPHY data
type.
Another feature of spatial data types
is that they support special spatial indexes. Unlike conventional indexes,
spatial indexes consist of a grid-based hierarchy in which each level of the
index subdivides the grid sector that is defined in the level above. But like
conventional indexes, the SQL Server query optimizer can use spatial indexes to
speed up the performance of queries that return spatial data.
HIERARCHYID
While hierarchical tree structures
are commonly used in many applications, SQL Server has not made it easy to
represent and store them in relational tables. In SQL Server 2008, the HIERARCHYID data type has been
added to help resolve this problem. It is designed to store values that
represent the position of nodes of a hierarchal tree structure.
For example, the HIERARCHYID data type makes it
easier to express these types of relationships without requiring multiple
parent/child tables and complex joins.
- Organizational structures
- A set of tasks that make up a larger projects (like a GANTT chart)
- File systems (folders and their sub-folders)
- A classification of language terms
- A bill of materials to assemble or build a product
- A graphical representation of links between web pages
Unlike standard data types, the HIERARCHYID data type is a CLR
user-defined type, and it exposes many methods that allow you to manipulate the
date stored within it.
For example, there are methods to get
the current hierarchy level, get the previous level, get the next level, and
many more. In fact, the HIERARCHYID data
type is only used to store hierarchical data; it does not automatically
represent a hierarchical structure. It is the responsibility of the application
to create and assign HIERARCHYID values
in a way that represents the desired relationship.
FILESTREAM
SQL Server is great for storing
relational data in a highly structured format, but it has never been
particularly good at storing unstructured data, such as videos, graphic files,
Word documents, Excel spreadsheets, and so on. In the past, when developers
wanted to use SQL Server to manage such unstructured data, developers
essentially had two choices. They could store unstructured data in
VARBINARY(MAX) columns inside the database; or they could store the data
outside of the database as part of the file system, and include pointers inside
a column that pointed to the file’s location. This allowed an application that
needed access to the file to find it by looking up the file’s location from
inside a SQL Server table.
FILESTREAM storage offers these
additional benefits:
Transact-SQL can be used to SELECT,
INSERT, UPDATE, DELETE FILESTREAM data.
By default, FILESTREAM data is backed up and
restored as part of the database file. If you want, there is an option
available so you can backup a database without the FILESTREAM data.
The size of the stored data is only
limited by the available space of the file system. Standard VARBINARY(MAX) data
is limited to 2 GB.
As you might expect, using FILESTREAM storage is not right for
every situation, for example, it is best used under the following conditions:
- When the BLOB file sizes average 1MB or higher.
- When fast read access is important to your application.
- When applications are being built that use a middle layer for application logic.
- When encryption is not required, as it is not supported for FILESTREAM data.
No comments :
Post a Comment