New Data types in Sql Server 2008

Sql server 2008 introduces new types that help the developer to have various choices to deal with various data.


Note:

In previous version "The DATETIME data type stores both the date and the time portion together as single column value. This data type supported dates between January 1, 1753 and December 31, 9999, with the time portion being accurate to 3.33 milliseconds and required 8 bytes of storage space. The SMALLDATE data type requires less storage space then a DATETIME data type, only 4 bytes, but supports a smaller date and time range. SMALLDATE only supports dates from January 1, 1900 through June 6, 2079, and the time portion is only accurate down to the minute."

Now let's see the now data types which introduced with sql server 2008.

Date Type (3 byte)
When you don't need the time portion of a DATETIME, you can use it

DECLARE @RES DATE
SET @RES = GETDATE()
SELECT @RES

Time Type (The size can be 3, 4, or 5 bytes, depending on the chosen precision)
When you don't need the date portion of a DATETIME, you can use it

DECLARE @RES TIME
SET @RES = GETDATE()
SELECT @RES

-- The default precision is 7 digits
-- you can display from 1 to 7 digits
DECLARE @RES2 TIME(7)  
SET @RES2 = GETDATE()
SELECT @RES2

DATETIME2 type(6 to 8 bytes)
DATETIME2 shows the greatest range of precision of date and time

DECLARE @RES DATETIME2
SET @RES = GETDATE()
SELECT @RES

--Note number of precision between the two results
DECLARE @RES2 DATETIME
SET @RES2 = GETDATE()
SELECT @RES2

DATETIMEOFFSET type (8 to 10 bytes)
Here provide you with extra time zone

DECLARE @RES DATETIMEOFFSET
SET @RES = GETDATE()
SELECT @RES

HIERARCHYID type (stored as varbinary <= 900 bytes)

Hierarchal data is defined as a set of data items related to one another in a hierarchy, that is, a parent node has a child and so forth
We use this type to store hierarchical data to simplify the retrieve data instead of using recursive operation or other complex way. So if you remember ParentID or ReportToId column now with this type no need for this column as foreign key.
With HierarchyID, the data type stores the whole path to the current record in the “Id” column.

Note:
HIERARCHYID type is a SQL CLR UDT (Common Language Runtime User Defined Type).

Let's see this example:

USE TestDb
GO

--Create Table that contains Id as hierarchyid data type
CREATE TABLE Employees
(
    Id       HIERARCHYID NOT NULL PRIMARY KEY,
    FullName NVARCHAR(256) NOT NULL,
    
)
GO

--Insert some data to this table with its path
INSERT INTO Employees(Id, FullName) VALUES
    (hierarchyid::GetRoot(), 'Fadi Ahmad Abdulwahab'),
    ('/1/', 'Shadi Ahmad Abdulwahab'),                
    ('/1/1/', 'Tareq Ahmad Abdulwahab'),        
    ('/1/1/1/','Rola Ahmad abdulwahab'),
    ('/2/','Rana Ahmad abdulwahab'),
    ('/2/1/','Rasha Ahmad abdulwahab')                
GO

-- Now let's retrieve the data
SELECT 
-- Id stored as binary format
Id, 
-- complete path of current record in the tree
Id.ToString() 'Record Path',
-- level of current record in the tree
Id.GetLevel() 'Record  Level',
-- Get childs of parent start from first root not the root '/'
Id.GetAncestor(1) 'partents of /./..',
-- Get childs of parents start from second root not the first root '/1/'
Id.GetAncestor(2) 'Parents of /././ ...',
FullName
FROM Employees
GO

--Let's do some filtering
--Here i want to retrieve only all records thier parent is /2/
SELECT 
Id, -- stored as binary format
Id.ToString() 'Record Path', -- output path
Id.GetLevel() 'Record  Level', -- output level
FullName
FROM Employees
WHERE Id.IsDescendantOf('/2/') = 1
GO

Others methods supported with HIERARCHYID:

•  GetAncestor: Returns a HierarchyID that represents the parent of this HierarchyID node.
•  GetDescendant: Returns a child node of this HierarchyID node.