New Datatypes in SQL Server 2008

SQL Server 2008 has introduced 4 new datatype:

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

In SQL Server 2005 and earlier, SQL Server only offered two date and time data types: DATETIME and SMALLDATETIME. While they were useful in many cases, they had a lot of limitations, including:

• Both the date value and the time value are part of both of these data types, and you can’t choose to store one or the other. This often causes a lot of wasted storage (because you store data you don’t need or want); adds unwanted complexity to many queries because the data types often had to be converted to a different form to be useful; and often reduces performance because WHERE clauses with these data and time data types often had to include functions to convert them to a more useful form, preventing these queries from using indexes.
• They are not time-zone aware, which often requires extra coding for time-aware applications.
• Precision is only .333 seconds, which is often not granular enough for some applications.
• The range of supported dates is not adequate for some applications, and the range does not match the range of .NET CLR DATETIME data type, which requires additional conversion code.

To overcome these problems, SQL Server 2008 introduces four new date and time data types, which include:

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