70-451 – Designing Database Tables – New Data Types


Identify the appropriate usage of new data types. This objective may include but is not limited to: geography, geometry, hierarchyid, date, time, datetime2, datetimeoffset, varbinary (max) filestream

My Take

We will need to know what the new data types are in SQL 2008 as well as when it is appropriate to use them.

Datetime Data Type Changes

Dates prior to SQL 2008 were stored in data types which included both date and time data with a precision pre-defined by the data type choosen (either small date time or date time). With SQL 2008, the ability to define data types of either just the date or just the time as well as the ability to define the precision you would like to keep for the time by using the new datetime2, datetimeoffset or time data types has been added. Furthermore, the new date time offset data type allows the user more flexibility of controlling the date time in relationship to the time zone for more effective handling of systems which are spread across multiple time zones.

The following chart is from Date and Time Data Types and Functions:

Time 3 to 5 bytes Range of 00:00:00.0000000 to 23:59:59.9999999 Accuracy of 100 nanoseconds
Date 3 bytes Range of 0001-01-01 to 9999-12-31 Accuracy of 1 day
SmallDateTime 4 bytes Range of 1900-01-01 00:00:00 to 2076-06-06 23:59:00 Accuracy of 1 minute
DateTime 8 bytes Range of 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997 Accuracy 0.333 of Second
DateTime2 6 to 8 bytes Range of 0001-01-01 00:00:00[.0000000] to 9999-12-31 23:59:59[.9999999] Accuracy of 100 nanoseconds
DateTimeOffset 8 to 10 bytes Range of 0001-01-01 00:00:00[.0000000] to 9999-12-31 23:59:59[.9999999] in UTC Accuracy of 100 nanoseconds – Expressed as range between + 14 and -14 hours of time zone offset.



The new date data type is used to hold the date with no corresponding time. It is not a variable length.


The new time data type is used to hold the time of day with no corresponding date. It is variable length, with the default being the most precision (Accuracy of 100 nanoseconds). The column can have a precision between 0 and 7 (integer value) with each decimal being one more significant digit behind the decimal. Time (0 – 2) = 3 bytes, Time(3 – 4) = 4 bytes, Time(5 – 7) = 5 bytes.


This poorly named data type should be used in place of datetime for all future development. It contains both the date and time portions of the field with the ability to provide the needed precision for the time element. The column can have a precision between 0 and 7 (integer value) with each decimal being one more significant digit behind the decimal. Time (0 – 2) = 6 bytes, Time(3 – 4) = 7 bytes, Time(5 – 7) = 8 bytes.


Similar to the datetime2, the datetimeoffset allows the user to provide both date and time data in the field, as well as choosing the level of precision for the time. In addition, the user is able to provide the timezone offset, expressed as a value from -14:00 to 14:00 as the time zone relates to UTC. The time zone offset comes at an expense of an extra two bytes over the normal datetime2 (i.e. costs of 8, 9 or 10 bytes for the column depending on the time precision).

Note the use of the SWITCHOFFSET and TODATETIMEOFFSET functions.

Spatial Types

Each of the spatial types (Geography and Geometry) can be represented through one of the following types.

  • Point – 0 dimensional object representing a single location, may contain Z (elevation) and M (measure) values
  • MultiPoint – a collection of zero or more points. The boundary of a Multipoint instance is empty.
  • LineString – a one dimensional object representing a sequence of points and the line segments connecting them. A linestring instance must be formed by at least two distinct points and can also be empty.
  • MultiLineString – a collection of zero or more geometry or geography LineString Instances.
  • Polygon – A Polygon is a two-dimensional surface stored as a sequence of points defining an exterior bounding ring and zero or more interior rings. A Polygon instance can be formed from a ring that has at least three distinct points. A Polygon instance can also be empty.
  • MultiPolygon – a collection of zero or more polygons
  • GeometryCollection – a collection of zero or more geometry or geography instances.


The geography data type is a pre-defined common language runtime (CLR) data type which represents ellipsoidal (round earth) data, such as GPS latitude and longitude co-ordinates. There are many methods for the geography data type which can be used to declare, interrogate, and compare the geography data.


DECLARE @myPolygon geography;

SET @myPolygon = geography::STGeomFromText(‘POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))’, 4326);

SELECT @myPolygon.ToString() as myPolygon, @myPolygon.STArea() as myPolygonArea;


The geometry data type is a pre-defined common language runtime (CLR) data type which represents Euclidean (flat earth) data. The geometry data type uses similar methods as the geography data type, with the exception of those methods relating to round earth ideas. There are a few methods that geography data has that the geometry data does not (.filter(), etc)


INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText(‘LINESTRING (100 100, 20 180, 180 180)’, 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText(‘POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))’, 0));


The hierarchyid data type is a highly compact data type used to represent position within a hierarchy. It should be noted that a hierarchy represented by the hierarchy id is not necessarily a tree, and that the application, not the database, will need to be used to ensure the desired relationships. See the topic: Working with Hierarchyid Data, or explore the HierarchyID Tutorials.

Hierarchyid is a .net common language runtime (CLR) construct within the database and allows for the use of several methods.


  • GetAncestor – Returns a hierarchyid representing the nth ancestor of this object
  • GetDescendant – Returns a child node of the parent
  • GetLevel – Returns an integer representing the depth of the node in the tree
  • GetRoot – Returns the root of the tree.
  • IsDescendantOf – Returns a boolean detailing if this node is a descendant of the input node
  • Parse – The opposite of ToString(), this method converts the string representation of the hierarchy node.
  • Read – Only useable in CLR (not T-SQL), this converts the binary representation of the hierarchyid node.
  • GetReparentedValue – Returns a path to the new root and the old root.
  • ToString – Returns a string with the logical representation of the hierarchy of the node.
  • Write – Only useable in CLR (not T-SQL), this converts the node into a binary representation.

Varbinary(max) Filestream

Filestream is a way to integrate unstructured data (text files, images, videos, etc) as a varbinary(max) column with the NTFS file system. The user can modify and select the data with T-SQL Statements through a streaming interface provided by the Win32 file system interfaces. With this new feature, the SQL Server buffer pool IS NOT USED!

Filestream data is stored on FILESTREAM filegroups, which are a special type of filegroup that contains system directories instead of the files themselves. It should also be noted that encryption is not supported on FILESTREAM data.

This entry was posted in SQL and tagged , , . Bookmark the permalink.

3 Responses to 70-451 – Designing Database Tables – New Data Types

  1. Pingback: MCITP 70-451 Links Page | Destination: Change

  2. Current says:

    Both Geography and Geometry data types have the method filter .
    check here:

  3. Eric Wisdahl says:

    This was written for 2008. At that time, geography did not include the .filter method.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s