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.

Date and Time:

• 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.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 0: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:

• 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.

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 ins.

• 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

FILESTREAM:

FILESTREAM storage is implemented in SQL Server 2008 by storing VARBINARY(MAX) binary large objects (BLOBs) outside of the database and in the NTFS file system. While this sounds very similar to the older method of storing unstructured data in the file system and pointing to it from a column, it is much more sophisticated. Instead of a simple link from a column to an outside file, the SQL Server Database Engine has been integrated with the NTFS file system for optimum performance and ease of administration. For example, FILESTREAM data uses the Windows OS system cache for caching data instead of the SQL Server buffer pool. This allows SQL Server to do what it does best: manage structured data; and allows the Windows OS to do what
is does best: manage large files. In addition, SQL Server handles all of the links
between database columns and the files, so we don’t have to.

In addition, 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.

Reference:

'Brad's Sure Guide to SQL Server 2008', - by Brad McGehee , http://www.simple-talk.com/