|
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:
- GetAncestor - returns a hierarchyid that represents the nth ancestor of this hierarchyid node
- GetDescendant - returns a child node of this hierarchyid node
- GetLevel - returns an integer that represents the depth of this hierarchyid node in the whole hierarchy
- GetRoot - returns the root node of this hierarchy tree (static method)
- IsDescendant - returns true if the passed child node is a descendant of this hierarchyid node (static method)
- Parse - converts string representation of a hierarchy (i.e.'/2/1/' to a hierarchyid value (static method)
- Reparent - moves a node of a hierarchy to a new location withing the hierarchy
- ToString - returns a string representation of this hierarchyid node
Here is one example on how you can employ this data type:
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);
From this code snippet you can see that 'Ratko' is manager to 'Tvrtko' and 'Simon' should report to 'Tvrtko' as he is his direct supervisor. We can easily query to whom should 'Tvrtko' report by using the following line:
SELECT EmployeeName FROM ProjectStructure WHERE ProjectNode.GetAncestor(1) = (SELECT ProjectNode FROM ProjectStructure WHERE EmployeeName = 'Tvrtko')
Hierarchyid columns tend to be very compact because the number of bits required to represent a node in a tree depends on the average number of children for the node (commonly refered to as the node's fanout). So, a new node in an organizational hierarchy of 100.000 employees , with an average fanout of six levels would take around 5 bytes of storage.
GEOSPATIAL DATA TYPES
Yes, this is my house somewhere in the countryside... thanks to Google Earth! And it presents one small example of usage of spatial data types - types that identify geographic locations and shapes, primarly on our mother Earth. These can be your cottage, a company location, a road, a hidden treasure on some pirate island.
In fact, SQL Server 2k8 provides us with two different data types - a geography and a geometry data type.
The Geography Data Type
Geography data type works with 'round-earth' data, that factors the curved surface of the earth into account in its calculations. Position is given in longitude and latitude (not yet in height ;) ).
The Geometry Data Type
On the other hand, geometry type works with the 'flat-earth' or planar model. In this model, the earth is treated as flat surface beginning in one known point. This flat model doesn't take into account the curvature of the earth, so it's primary used for short distances, as building interior or metropolitan area.
All these data are standardized in format, using Open Geospatial Consortium (OGC) Simple Features for SQL Specification. You can find more information on this site.
|
