Systems Engineering and RDBMS

New Data types in SQL Server 2008 – I

Posted by decipherinfosys on August 25, 2007

SQL Server 2008 has quite a few new data-types. We will cover those in 2 parts as we start playing with those in our test environments.

1) DATE and TIME Data Types: Currently, SQL Server supports datetime and smalldatetime but there is no data-type that supports just the DATE part or just the TIME part and that is considered to be de-normalized by some data modelers since the same field is being used to represent both the date and the time piece. Not only that, in certain situations where one needs to record the different times related to a given date, Example: The trucks can take off from the warehouse at 8:00 A.M., 10:00 A.M., 12:00 P.M. and so on for a given day. One needed to store the date repeatedly for just capturing this time specific information. That is no longer the case with the introduction of these 2 new data types in SQL Server 2008. In prior versions, one then needed to apply the CONVERT() function in order to retrieve only what one needed. That won’t be necessary anymore.

The TIME data-type supports upto 100 nano seconds.

2) Datetime2 data type: The name is funny. MSFT could have come up with a better name (this does remind me of the Oracle VARCHAR and VARCHAR2 data-types :-)). This data type extends the granularity of the time. As you might already know, the granularity of the time in the datetime data type is 1/300th of a second i.e. you can store only .000, .003 or .007 seconds. This new data type extends that to 100 nano seconds just like the TIME data type. The fractional part can be specified by declaring the variable or a column as datetime2(n) where n is the precision with valid values from 0 to 7.

3) In order to support the TIMEZONES in a better way, MSFT has introduced DATETIMEOFFSET(n) data type. This support is provided in Oracle using the TIMEZONE family of data-types. In SQL Server 2008, one can use it to provide better support for the applications that require timezone support, Example: A Warehouse in Eastern Timezone that has it’s data center in central timezone and also has people entering data sitting in the Pacific timezone and this data is timezone sensitive due to shipping date and times. Likewise for a voting system with cut off timelines for the voters etc..

There are more new data types: Better User Defined Types Support: HIERARCHYID (for better support for hierarchical data), spatial data types (Geometry, Geography) and support of TYPES (this feature has been available in Oracle for ages now and will help make array processing and nesting of procedures a lot easier)., FILESTREAM (for better binary data storage) and there have been changes to the NTEXT, TEXT and IMAGE data-types as well which we will cover in the next part.

One Response to “New Data types in SQL Server 2008 – I”

  1. […] We had covered these data types in our blog post here. […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: