New Data Types in SQL Server 2008

SQL Server 2008 provides a number of useful new data types. These data types include

• HIERARCHYID: HIERARCHYID is a variable-length system data type that’s designed to address the problem of tracking tree structures such as organization charts. HIERACHYID can store data optimized for either depth or breadth.
• GEOGRAPHY: The integration of mapping capabilities into many applications makes the new SQL Server 2008 spatial data types, such as GEOGRAPHY, a welcome addition. The GEOGRAPHY data type is a Microsoft .NET Framework–based spatial data type and uses a round-earth model. This data type stores points, lines, polygons, and collections of latitude and longitude coordinates.
• GEOMETRY: GEOMETRY is another .NET-based and spatial data type. Unlike the GEOGRAPHY type, which is designed primarily for navigation and mapping on a round-earth model, the GEOMETRY data type is Open Geospatial Consortium (OGC) compliant and uses a flat-earth model.
• DATE: A native SQL data type, the new DATE data type is ANSI compliant and uses the format YYYY-MM-DD. The DATE data type can contain values from 0001-01-01 to 9999-12-31.
• TIME: Another native SQL data type, TIME complements the new DATE data type. TIME uses an hh:mm:ss[.nnnnnnn] format and can contain values from 00:00:00.0000000 to 23:59.59.9999999.
• DATETIME2: The DATETIME2 data type addresses the need for more precise date-time storage and is accurate up to 100 nanoseconds. The DATETIME2 data type uses the format YYYY-MM-DD hh:mm:ss[.nnnnnnn] and can store values ranging from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59.59.9999999.
• DATETIMEOFFSET: The most advanced of the new date-time data types, DATETIMEOFFSET is like DATETIME2 but is time-zone aware. DATETIMEOFFSET use the format YYYY-MM-DD hh:mm:ss[.nnnnnnn] and can store values ranging from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59.59.9999999.
• FILESTREAM: The FILESTREAM data type combines the performance of file-system streaming APIs with the transactional integrity of a relational database. The FILESTREAM data type enables SQL Server to combine unstructured data and is stored in the NTFS file system with the relational database engine. The SQL Server engine manages the link between the FILESTREAM columns and files in the file system, including backing up and restoring file system data.