SQL Server 2008 - New Data Types

SQL Server 2008 offers several new data types. Some of the changes are really just improvements on existing data types. For example the four new date time data types will offer more range, precision and flexibility than the traditional datetime data type.

Also, we have access to some brand new capabilities. SQL 2008 will introduce spatial data, the HierarchyID and the Filestream data type. These capabilities will help to further increase SQL Servers stronghold in the database market by giving developers more power and flexibility in how they store and retrieve their data.

Four New Date Time Data Types:

• datetime2 - allows for increased range for the year and accuracy down to 100 nanoseconds. The min date is 0001-01-01 and the max date is 9999-12-31.
• date - Just the date and only the date. No time.
• time - The time only.

• datetimeoffset - Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. Same range and precision as datetime2 and also allows for a time zone offset of +/- 14 hours.

The Hierarchy ID Data Type:

The Hierarchy id data type allows you to construct relationships among data elements within a table, specifically to represent a position in a hierarchy. This is a perfect fit for representing tree-like structures such as organizational charts. In order to manipulate this data several new functions have been created:

• GetLevel() -gets the level of the current node.
• GetRoot() - returns the root of the tree.
• IsDescendent() - returns true/false if the node is a descendent of the parent.
• Parse() - converts the canonical string representation of a hierarchyid to a hierarchyid value.
• Read() - reads binary representation of HierarchyId from the passed-in BinaryReader and sets the SqlHierarchyId object to that value.
• Write() - writes out a binary representation of SqlHierarchyId to the passed-in BinaryWriter.
• Reparent() - Used to re-parent a node.

• ToString() - self explanatory

Spatial Data:

Spatial data types are the backbone of Geographical Information Systems (GIS).  GIS technology can be used for scientific investigations, resource management, asset management, archaeology, environmental impact assessment, urban planning, cartography, criminology, geographic history, marketing, logistics, and other purposes. Google Earth is a good example of a Geographical Information System.

Geography and Geometry

The Geography data type uses the same methods but the data type reflects the fact the we live on a curved 2D surface popularly known as the earth.

The Geometry data type supports a flat 2D surface with XY coordinates for points.  Points can be on lines, on line string and mark the edges of polygons.  Here are some of the new functions that were created to be used on Geometry and Geography Data Types:

• STIntersects() - Returns 1 if a geometry instance intersects another geometry instance. Returns 0 if it does not.
• STArea() - Returns the total surface area of a geography instance.

• STDistance() - Returns the shortest distance between a point in a geography instance and a point in another geography instance.

FileStream Data Type:

Allows large binary data to be stored in the file system and yet remain an integral part of the database with transactional consistency. This enables scale-out of large binary data to be managed by the database and yet stored on cheaper storage and file systems. This would be perfect for document management systems like SharePoint. Hopefully, this is one of the driving forces behind Microsoft creating it.

Applications can use a new storage type VARBINARY(MAX) FILESTREAM to define table columns and store large binary data as files in the file system instead of as a SQL BLOB. Applications can use T-SQL SELECT/INSERT/UPDATE/DELETE statements to query and modify FILESTREAM data.