Identify the appropriate usage of new data types

May include but is not limited to: geography, geometry, hierarchyid, date, time, datetime2, datetimeoffset, varbinary(max), FILESTREAM

SQL Server 2008 comes with a bunch of new types addressing long-standing issues (such as the need for a date type) and some new functionality in the shape of the spatial (geometry and geography) types. I must admit that I’ll focus mostly for the date and time types, and varbinary(max), because I use these daily, and because I don’t understand the mathematical concept behind hierarchyid, and was never the fan of spatial data.

So, what’s new in dates and times? The following table will explain:

Date A type dedicated to store date information- from 0000-00-00 till 9999-12-31. A wide range! A fixed value.
Time A dedicated type for time values, can store up to 7 fractional second digits (the precision is 100 nanoseconds). You supply the number of digits as a parameter.
Datetime2 The former two together – the range of date with the precision of time!
Datetimeoffset Datetime2 plus time zone information – from -14:00 to +14:00.


The new data types ship with new functions:

  • SYSDATETIME() – returns the current system (Windows) time as a datetime2 value.
  • SYSUTCDATETIME() – returns the current UTC time as a datetim2 value.
  • SYSDATETIMEOFFSET() – returns the current system time with offset information.
  • TODATETIMEOFFSET() – converts a given datetime type to datetimeoffset.

And finally, there are some new options to the DATEPART function:

  • Microsecond (mcs)
  • Nanosecond (ns)
  • TZoffset (tz)
  • ISO_WEEK (isowk, isoww)

With SQL Server 2005 the advent of the max data types have come. Text, ntext and image are now considered to be deprecated, and instead of them, we can use varchar(max), nvarchar(max) and varbinary(max) – which can hold 2 GBs of data (in a filestream solution, this limit is gets up to the available disk space).

Now you have two options on where to store these types – in row, or in the file system. FILESTREAM is the way to go when the following is true:

  • The average size of max data is larger than one MB.
  • Fast read access is important.
  • Middle tier is present in the application.

To specify FILESTREAM first enable it on the database level. Then simply mark your candidates with the FILESTREAM attribute in a CREATE TABLE clause, and you’re good to go.

The cardinal advantage can be gained with FILESTREAM is that you save a lot of memory for processing purposes, which would otherwise be used to store the varbinary data.

We have geography and geometry – two interesting new types which I know nothing about. A little fact that may be helpful: geometry uses the (X, Y) coordinates, while geography uses (LAT, LON). The problem is that latitude is the Y axis, and longitude is X, so it maps to (Y, X). Be aware of that.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s