New Data Types in SQL Server 2008

In this article we will discuss about the new Data Types introduced in SQL Server 2008 with examples.

New Data Types

Date/Time Data Types
  • DATE
  • TIME
  • DATETIME2
  • DATETIMEOFFSET

LOB Data Types

  • FILESTREAM

Organizational Data Types

  • HIERARCHYID

Spatial Data Types

  • GEOGRAPHY
  • GEOMETRY

Data/Time Data Types

DATE

The new DATE data type uses the format YYYY-MM-DD and is ANSI-complaint.
DATE data type is a native SQL data type, and it contains values from 0001-01-01 to 9999-12-31 and it uses 3 bytes of storage.

Example:
DECLARE @ChDate DATE
SET @ChDate = '11/16/1982'
PRINT @ChDate

Output:
1982-11-16

TIME

TIME is another native SQL data type, uses hh:mm:ss[.nnnnnnn] format and ANSI-complaint.
TIME can contain values from 00:00:00.0000000 to 23:59:59.9999999 and it requires 3 to 5 bytes of storage.

Example:
DECLARE @ChTime TIME
SET @ChTime = GETDATE()
PRINT @ChTime

Output:
22:55:24.9830000

Note: By default TIME data type utilizes the maximum amount of storage and is created with 7 nanosecond decimal places. To reduce the storage, you can specify the number of decimal positions in the deceleration.
Example:
DECLARE @ChTime TIME(2)
SET @ChTime = GETDATE()
PRINT @ChTime

Output:
22:55:24.98

DATETIME2

The new DATETIME2 data type is accurate upto 100 nanoseconds and uses the format YYYY-MM-DD hh:mm:ss[.nnnnnnn].
The DATETIME2 data type can stores values ranging from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 and uses 6 to 8 bytes of storage.

Example:
DECLARE @ChDate DATETIME2
SET @ChDate = GETDATE()
PRINT @ChDate

Output:
2012-07-10 23:11:17.7630000

DATETIMEOFFSET

DATETIMEOFFSET is time zone aware.
DATETIMEOFFSET  uses the format YYYY-MM-DDhh:mm:ss[.nnnnnnn] and can stores values ranging from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59.59.9999999.

Example:
DECLARE @ChDate DATETIMEOFFSET
SET @ChDate = GETDATE()
PRINT @ChDate

Output:
2012-07-10 23:16:04.0900000 -05:30

LOB Data Types

.........................