What’s New in SQL 2008 – Part 2 – Data Types

Date and Time

Data Type	Size (bytes)	Example	Notes	Client Providers
DATE	3	'2009-10-01'		OLEDB: DBTYPE_DBDATE ADO.Net: DateTime ODBC: SQL_DATE
TIME	3-5	'13:12:00.1234567'	Accurate to 100 nanoseconds	OLEDB: DBTYPE_DBTIME ADO.Net: TimeSpan ODBC: SQL_TIME
DATETIME2	6-8	'2009-10-01 13:12:00.1234567'	Accurate to 100 nanoseconds Can specify precision ie decimal places eg DATETIME(3)	OLEDB: DBTYPE_DBTIMESTAMP ADO.Net: DateTime ODBC: SQL_TIMESTAMP
DATETIMEOFFSET	8-10	'2009-10-01 13:12:00.1234567+02:00'	Accurate to 100 nanoseconds	OLEDB: DBTYPE_DBTIMESTAMP ADO.Net: DateTime ODBC: SQL_TIMESTAMP

There are a number of new functions which can also be used with these new datatypes including:

• SYSDATETIME (returns DATETIME2), SYSUTCDATETIME, SYSDATETIMEOFFSET (includes time zone in return value) – Current Date and Time
  
• SWITCHOFFSET – to change the timezone for a DATETIMEOFFSET value
  
• TODATETIMEOFFSET – convert non-timezone-aware values to time-zone-aware
  

Other

Data Type	Size (bytes)	Useage	Returns	Notes
HIERARCHYID	VARBINARY eg 5 bytes for 100k records at 6-Levels	DECLARE @h hierarchyid = hierarchyid::GetRoot() DECLARE @c hierarchyid SET @c = @h.GetDescendant(NULL, NULL) SELECT @c.ToString()	'/1/'	Represents position in a hierarchy Actually a CLR function with several methods eg GetDescendant/IsDescendantOf GetLevel/GetRoot GetReParentedValue ToString/Read/Write (CLR)