Seven new data types are being built into SQL Server 2008, and they provide the means for working with and simplifying the management of more complicated data.
Frankly, there are extensions in two fields: time and spatial information. I'll present in this post these brave ones...
The Date Data Type
The problem with the old SQL Server's datetime data type was that users did not have the ability to work with date and time information separately. Date data type now stores only date component, ranged from January 1, 1000 to December 31, 9999. Each date variable requires only 3 bytes and has a precision of 10 digits. So, the unit is a single day.
The Time Data Type
Time data type separates time component from the old datetime data type. It deals with hours, minutes, seconds and fractions of seconds. It is based on a 24-hour clock, and has supported range from 00:00:00.0000000 to 23:59:59.99999999 (dot separates seconds and its fractions). The default precision is 7 digits, but you can adjust it as you like when you create the column. The accuracy equals to 100ns.
CREATE TABLE WorkingHours
(
EmployeeID uniqueidentifier,
WorkingDay date,
StartTime time,
EndTime time
)
The Datetimeoffset Data Type
This data type provides time-zone information along with the datetime information. It is indicated with plus or minus sign before time part of data. So, the format of one data could be: '2008-10-21T08:34:00.1234567+01:00', i.e. October 21 2008, 08:34 AM in +1 hour zone (Zagreb, Wien). It is notable to say that it is depicted in ISO 8601 format - a standard format for date and time data types.
The Datetime2 Data Type
Datetime2 provides us with more precise datetime data type. More exactly, it ranges from January 1, 0000 (instead of January 1, 1753) through December 31, 9999. The precision of the time component is the same as in time data type, so 7 fractional seconds. The original datetime type provided three digits of precision and a time range of 00:00:00 through 23:59:59.999.
The Hierarchyid Data Type
Now we comes to something very cool - a special type for helping us storing hierarchy structured data.
Imagine that you need to create a table with some project structure in which there are developers responsible to project managers and project managers responsible to chief executive officiers. You would normally implement this as one table with some additional key depicting this relation. Well, the hierarchyid is such a key, in fact, it resembles this structure and it is being filled with the corresponding values by using special functions, as presented below:
CREATE TABLE ProjectStrucure
(
EmployeeId uniqueidentifier NOT NULL,
EmployeeName varchar(50) NOT NULL,
ProjectNode hierarchyid NOT NULL
);
DECLARE @manager hierarchyid = hierarchyid::GetRoot();
DECLARE @employee hierarchyid;
INSERT INTO ProjectStructure VALUES (NEWID(), 'Ratko', @manager);
SET @employee = @manager.GetDescendant(NULL, NULL);
INSERT INTO ProjectStructure VALUES (NEWID(), 'Tvrtko', @employee);
SET @employee = @manager.GetDescendant(@employee, NULL);
INSERT INTO ProjectStructure VALUES (NEWID(), 'Simon', @employee);
SELECT EmployeeName FROM ProjectStructure WHERE ProjectNode.GetAncestor(1) = (SELECT ProjectNode FROM ProjectStructure WHERE EmployeeName = 'Tvrtko')