SQL Server 2008 RTM Released! BTW, What is RTM?

SQL Server 2008 RTM is released (6th August 2008). In the last few weeks, there had been a lot of discussions in the news groups and forums about the RTM date. And the wait is over now. If you search for 'SQL Server 2008 RTM' in your favorite search engine, you could get to hundreds of blog posts about the release and many of them briefly describe the major features included in the release. You could find a few here: http://blogs.msdn.com/wesleyb/archive/2008/08/07/sql-server-2008-rtm.aspx

So what is RTM?

RTM stands for 'Release To Manufacturing'.

But what does that mean?

RTM is the stage at which the distribution process begins. CD/DVDs will be created and then retail packs and finally the product will be shipped. This entire process might be quite lengthy and hence the RTM date is usually much ahead of the final release date.

Another related term is RTW which stands for Release To Web. At this stage the product is ready for download from a public web site. I never realized that the process of releasing a product could be such a lengthy process, until I read these posts by Canthe, a release manger at Microsoft.

What does RTM/RTW mean? (part 1)

What does RTM/RTW mean? (Part 2)

SQL Server 2008 (KATMAI) TSQL Enhancements - Part 9 - HIERARCHYID::GetDescendant()

In the previous post, we have had a detailed look into 3 methods exposed by the HIERARCHYID data type. We have examined GetAncestor(), GetRoot() and GetLevel(). In this post, we will see some examples that demonstrate the usage of a few more methods.

GetDescendant()

This method is little more complex than the other methods we have seen so far. I think the only case where I would need this function is to generate a Hierarchy ID for a new row that I want to insert at a specific location. Let us see a few examples. Before we could run the examples, we need to create some sample data. Lets us use a slightly modified version of the script we used in the previous session.

/*
  CEO
      PM1
          TechLead1
              Jacob
              Steve
              Thomson
              Cheryl
          TechLead2  
              Bob
              Mike
*/
-- ----------------------------------------------------------
IF OBJECT_ID('employees') IS NOT NULL DROP TABLE employees
CREATE TABLE employees (
              EmployeeNode HIERARCHYID,
              EmployeeID INT,
              EmployeeName VARCHAR(20),
              ManagerID INT )
            
INSERT INTO employees (EmployeeNode, EmployeeID, EmployeeName, ManagerID)
VALUES
  (CAST('/' AS HIERARCHYID), 1, 'CEO', 0),
  (CAST('/1/' AS HIERARCHYID), 2, 'PM1', 1),
  (CAST('/1/1/' AS HIERARCHYID), 4, 'TechLead1', 2),
  (CAST('/1/1/1/' AS HIERARCHYID), 6, 'Jacob', 4),
  (CAST('/1/1/2/' AS HIERARCHYID), 7, 'Steve', 4),
  (CAST('/1/1/3/' AS HIERARCHYID), 8, 'Thomson', 4),
  (CAST('/1/1/4/' AS HIERARCHYID), 9, 'Cheryl', 4),
  (CAST('/1/2/' AS HIERARCHYID), 5, 'TechLead2', 2),
  (CAST('/1/2/1/' AS HIERARCHYID), 10, 'Bob', 5),
  (CAST('/1/2/2/' AS HIERARCHYID), 11, 'Mike', 5)

Hierarchy ID controls the order of items in a hierarchy. Hence we need to generate a correct Hierarchy ID when we insert a new item to the hierarchy. If we need to insert a new item at a specific location in a hierarchy, we need to generate a hierarchy ID that represents that location and then perform the insert operation. This is the place where I think GetDescendant() can help.

Let us see an example. Assume that we need to add one more developer under TechLead1. Before we add the row, we need to generate a Hierarchy ID that represents the location after Cheryl. The following sample shows how to do this.

-- find the node of TechLead1
DECLARE @TechLeadNode HIERARCHYID
SELECT @TechLeadNode = EmployeeNode FROM Employees WHERE EmployeeName = 'TechLead1'

-- Find the maximum Node ID under TechLead1
DECLARE @MaxNode HIERARCHYID
SELECT @MaxNode = MAX(EmployeeNode) FROM Employees
WHERE EmployeeNode.GetAncestor(1) = @TechLeadNode

-- Generate an ID under TechLead1 and after the last row
DECLARE @DavidNode HIERARCHYID
SELECT @DavidNode = @TechLeadNode.GetDescendant(@MaxNode, NULL)

-- insert the row
INSERT INTO employees (EmployeeNode, EmployeeID, EmployeeName, ManagerID)
SELECT @DavidNode, 12, 'David', 4

-- select 
SELECT
  EmployeeName,
  EmployeeNode.GetLevel() AS Level,
  EmployeeNode.ToString() AS Hierarchy
FROM Employees
WHERE EmployeeNode.GetAncestor(1) = @TechLeadNode
ORDER BY EmployeeNode
/*
EmployeeName         Level  Hierarchy
-------------------- ------ ---------------------------
Jacob                3      /1/1/1/
Steve                3      /1/1/2/
Thomson              3      /1/1/3/
Cheryl               3      /1/1/4/
David                3      /1/1/5/
*/

GetDescendent(Child1, NULL) returns an ID which is greater than Child1. That is what we did here. We identified highest value currently in the table by using the MAX() function and passed it as the first parameter to the function. In the above case, we inserted a row as the last child under the given node. Now let us see how to add a row as the first item.

-- find the node of TechLead1
DECLARE @TechLeadNode HIERARCHYID
SELECT @TechLeadNode = EmployeeNode FROM Employees WHERE EmployeeName = 'TechLead1'

-- Find the maximum Node ID under TechLead1
DECLARE @MinNode HIERARCHYID
SELECT @MinNode = MIN(EmployeeNode) FROM Employees
WHERE EmployeeNode.GetAncestor(1) = @TechLeadNode

-- Generate an ID under TechLead1 before the first row
DECLARE @TonyNode HIERARCHYID
SELECT @TonyNode = @TechLeadNode.GetDescendant(NULL, @MinNode)

-- insert the row
INSERT INTO employees (EmployeeNode, EmployeeID, EmployeeName, ManagerID)
SELECT @TonyNode, 13, 'Tony', 4

-- select 
SELECT
  EmployeeName,
  EmployeeNode.GetLevel() AS Level,
  EmployeeNode.ToString() AS Hierarchy
FROM Employees
WHERE EmployeeNode.GetAncestor(1) = @TechLeadNode
ORDER BY EmployeeNode
/*
EmployeeName         Level  Hierarchy
-------------------- ------ -------------------------
Tony                 3      /1/1/0/
Jacob                3      /1/1/1/
Steve                3      /1/1/2/
Thomson              3      /1/1/3/
Cheryl               3      /1/1/4/
David                3      /1/1/5/
*/

Note that a new hierarchy ID is generated with '/1/1/0/'. If we try to add one more row before 'Tony', another node will be generated with value '/1/1/-1/' and so on. Now let us see how to insert a row at the same level, but between Steve and Thomson.

-- find the node of TechLead1
DECLARE @TechLeadNode HIERARCHYID
SELECT @TechLeadNode = EmployeeNode FROM Employees WHERE EmployeeName = 'TechLead1'

DECLARE @SteveNode HIERARCHYID, @ThomsonNode HIERARCHYID
SELECT @SteveNode = EmployeeNode FROM Employees WHERE EmployeeName = 'Steve'
SELECT @ThomsonNode = EmployeeNode FROM Employees WHERE EmployeeName = 'Thomson'

-- Generate an ID under TechLead1 between Steve and Thomson
DECLARE @MikeNode HIERARCHYID
SELECT @MikeNode = @TechLeadNode.GetDescendant(@SteveNode, @ThomsonNode)

-- insert the row
INSERT INTO employees (EmployeeNode, EmployeeID, EmployeeName, ManagerID)
SELECT @MikeNode, 14, 'Mike', 4

-- select 
SELECT
  EmployeeName,
  EmployeeNode.GetLevel() AS Level,
  EmployeeNode.ToString() AS Hierarchy
FROM Employees
WHERE EmployeeNode.GetAncestor(1) = @TechLeadNode
ORDER BY EmployeeNode
/*
EmployeeName         Level  Hierarchy
-------------------- ------ --------------------------
Tony                 3      /1/1/0/
Jacob                3      /1/1/1/
Steve                3      /1/1/2/
Mike                 3      /1/1/2.1/
Thomson              3      /1/1/3/
Cheryl               3      /1/1/4/
David                3      /1/1/5/
*/

GetDescendant() generates a new ID based on the value of child1 and child2. If child1 and child2 are NULL, then a child value of the parent is returned. If Child1 is not null and child2 is null, a value greater than child1 is returned. If child2 is not null and child1 is null, a value smaller than child2 is returned. If child1 and child2 are not empty, a value that is between child1 and child2 is returned.

SQL Server 2008 (KATMAI) TSQL Enhancements

HIERARCHYID Error: Could not find method 'IsDescendant'

You might get the following error while working with HIERARCHYID data types in RC0:

Could not find method 'IsDescendant' for type 'Microsoft.SqlServer.Types.SqlHierarchyId' in assembly 'Microsoft.SqlServer.Types'

The IsDescendant() method of HIERARCHYID data type is renamed to IsDescendantOf() in RC0. To correct the error, change parent.IsDescendant(child) to child.IsDescendantOf(parent). This is documented at http://download.microsoft.com/download/4/9/e/49eeb41a-a769-4520-80d6-671b8ae2bd06/ReleaseNotesSQLServer2008.htm

Also note that the method names are case sensitive.

HIERARCHYID Error: Could not find method 'Reparent'

If you are using SQL Server 2008 RC0 and try to run a piece of code written with the previous version, using the Reparent() method of HIERARCHYID data type, you will get the following error:

Could not find method 'Reparent' for type 'Microsoft.SqlServer.Types.SqlHierarchyId' in assembly 'Microsoft.SqlServer.Types'

This method is renamed to GetReparentedValue in RC0. Use GetReparentedValue() instead of Reparent().

SQL Server 2008 (KATMAI) TSQL Enhancements - Part 8 - HIERARCHYID (Part 2)

In the previous post, we have examined the new HIERARCHYID data type introduced by SQL Server 2008. HIERARCHYID is a CLR data type and it supports a number of methods.

Note that the methods of CLR data types are case sensitive. Most of us, who do not bother much about the casing should be very careful working with these types. So, pay special attention to the correct casing while working with CLR data types.

We will examine the methods supported by HIERARCHYID data type in this post.

HIERARCHYID exposes the following methods.

1. GetAncestor()
2. GetDescendant()
3. GetLevel()
4. GetRoot()
5. ToString()
6. IsDescendantOf()
7. Parse()
8. Read()
9. GetReparentedValue()
10. Write()

We have seen some of these methods in the previous post. Let us have a closer look into each of these methods. Before we examine those methods and see them in action, we need to create a table and populate it with some hierarchical data. Run the code given in the previous post to create a table named 'employees' and populate it.

GetAncestor()

This method returns the HIERARCHYID of the parent of a given node. This method takes an argument which specifies the number of levels to go up. For example, GetAncestor(0) returns the node itself, and GetAncestor(1) returns the immediate parent. GetAncestor(2) returns the parent's parent (Parent at 2 levels up). Let us test this.

DECLARE @h HIERARCHYID
SELECT @h = EmployeeNode FROM employees WHERE EmployeeName = 'Andrew'
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(0)
/*
EmployeeName
--------------------
Andrew 
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(1)
/*
EmployeeName
--------------------
TechLead4
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(2)
/*
EmployeeName
--------------------
PM2
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(3)
/*
EmployeeName
--------------------
CEO
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h.GetAncestor(4)
/*
EmployeeName
--------------------

*/

Note that GetAncestor(4) did not return data. There are only 3 levels exist above the node of 'Andrew' and hence GetAncestor(4) returns NULL.

Note: If you pass a value higher than the number of levels in the hierarchy, you will get an empty result set. However, if you pass a negative number, SQL Server will generate an error.

In the previous examples we applied a WHERE condition to find out "Who is the ancestor of X". This helped us to traverse the hierarchy upward. Now let us see the reverse of this. Let us find out "Whose ancestor is X?" which will help us to traverse the hierarchy downward. Let us see a few examples.

DECLARE @h HIERARCHYID
SELECT @h = EmployeeNode FROM employees WHERE EmployeeName = 'PM2'
SELECT EmployeeName FROM employees WHERE EmployeeNode.GetAncestor(1) = @h
/*
EmployeeName
--------------------
TechLead3        
TechLead4 
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode.GetAncestor(2) = @h
/*
EmployeeName
--------------------
Laura            
Jeff             
Bill             
Andrew  
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode.GetAncestor(3) = @h
/*
EmployeeName
--------------------

*/

Note that the last query did not return anything. If the level does not exist, an empty result set is returned. However, if you pass a negative number, SQL Server will generate an error.

GetRoot()

This is a static method. The word "static" may not be that familiar to many SQL Server developers. 'static' methods can be invoked without instantiating an instance of an object. GetRoot() returns a HIERARCHYID that points to the root of any hierarchy. Let us see an example.

DECLARE @h HIERARCHYID = HierarchyID::GetRoot()
SELECT EmployeeName FROM employees WHERE EmployeeNode = @h
/*
EmployeeName
--------------------
CEO              
*/

Instead of assigning the result of the GetRoot() method a variable, we could even use it directly in the WHERE clause. Here is an example.

SELECT EmployeeName FROM employees WHERE EmployeeNode = HierarchyID::GetRoot()
/*
EmployeeName
--------------------
CEO
*/

GetLevel()

GetLevel() returns the level of a given node in the hierarchy. To understand this better, let us look at a few examples.

SELECT EmployeeName FROM employees WHERE EmployeeNode.GetLevel() = 0
/*
EmployeeName
--------------------
CEO  
*/
SELECT EmployeeName FROM employees WHERE EmployeeNode.GetLevel() = 1
/*
EmployeeName
--------------------
PM1              
PM2
*/
SELECT EmployeeName, EmployeeNode.GetLevel() AS Level
FROM employees
ORDER BY EmployeeNode.GetLevel()
/*
EmployeeName         Level
-------------------- ------
CEO                  0
PM1                  1
PM2                  1
TechLead3            2
TechLead1            2
TechLead2            2
TechLead4            2
Bill                 3
Andrew               3
Bob                  3
Mike                 3
Jacob                3
Steve                3
Laura                3
Jeff                 3
*/

We will examine the other methods in a future post.

SQL Server 2008 (KATMAI) TSQL Enhancements

1. SQL Server 2008 (KATMAI) TSQL Enhancements -
   Part 1 - Variable initialization and Compound Assignment Operators
   
2. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 2 - Row Constructors
   
3. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 3 - New Data Type: DATE
   
4. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 4 - New Data Type: TIME
   
5. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 5 - New Data Type: DATETIME2
   
6. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 6 - DATETIMEOFFSET
   
7. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 7 - HIERARCHYID (Part 1)

SQL Server 2008 (KATMAI) TSQL Enhancements - Part 7 - HIERARCHYID (Part 1)

HIERARCHYID is a new data type introduced in SQL Server 2008. I had worked on a few assignments where I had to deal with hierarchical data. I guess most of you have at least once worked with a hierarchical data model. Traversing a hierarchy is not very easy in SQL Server 2005. The new data type HIERARCHYID is all set to make the life easier.

Let us first see a simple example and then examine the data type in detail. For the purpose of the example, let us create a table to store some hierarchical data that looks like the following.

-- ----------------------------------------------------------
--    CEO
--        PM1
--            TechLead1
--                Jacob
--                Steve
--            TechLead2    
--                Bob
--                Mike
--        PM2
--            TechLead3
--                Laura
--                Jeff
--            TechLead4
--                Bill
--                Andrew
-- ----------------------------------------------------------

As you must have figured out so far, this is the organizational hierarchy of a technology company. Now let us create a table and populate it with the above data.

IF OBJECT_ID('employees') IS NOT NULL DROP TABLE employees
CREATE TABLE employees (
             EmployeeID INT,
             EmployeeName VARCHAR(20),
             ManagerID INT )
          
INSERT INTO employees (EmployeeID, EmployeeName, ManagerID)
SELECT 1, 'CEO', 0 UNION ALL
SELECT 2, 'PM1', 1 UNION ALL
SELECT 4, 'TechLead1', 2 UNION ALL
SELECT 6, 'Jacob', 4 UNION ALL
SELECT 7, 'Steve', 4 UNION ALL
SELECT 5, 'TechLead2', 2 UNION ALL
SELECT 8, 'Bob', 5 UNION ALL
SELECT 9, 'Mike', 5 UNION ALL
SELECT 3, 'PM2', 1 UNION ALL
SELECT 10, 'TechLead3', 3 UNION ALL
SELECT 11, 'Laura', 10 UNION ALL
SELECT 12, 'Jeff', 10 UNION ALL
SELECT 13, 'TechLead4', 3 UNION ALL
SELECT 14, 'Bill', 13 UNION ALL
SELECT 15, 'Andrew', 13

Looks pretty simple. Now we could easily answer the following questions.

To whom does Mike Report?

SELECT
 e.EmployeeName,
 p.EmployeeName AS Manager
FROM employees e
INNER JOIN employees p ON p.EmployeeID = e.ManagerID
WHERE e.EmployeeName = 'Mike'
/*
EmployeeName         Manager
-------------------- --------------------
Mike                 TechLead2        
*/

Who are the developers reporting to TechLead3?

SELECT
 e.EmployeeName,
 p.EmployeeName AS Manager
FROM employees e
INNER JOIN employees p ON p.EmployeeID = e.ManagerID
WHERE p.EmployeeName = 'TechLead3'
/*
EmployeeName         Manager
-------------------- --------------------
Laura                TechLead3        
Jeff                 TechLead3
*/

Now look at the following questions:

1. Who are the bosses of TechLead4?
   
2. Who are at the second level below PM2?
   
3. Who are the employees directly or indirectly reporting to PM2?
   

We could answer the questions easily when it is about people one level above or below a given position. However, it will be harder to find information from more than one level up or down. HIERARCHYID makes this much easier. To find all the bosses of TechLead4, we need to go upward in the hierarchy starting from the immediate boss of TechLead4. To find the people at second level below PM2, we need to find all the people who directly reports to PM2 and all the people below them. To answer the 3rd question, we need to traverse the entire hierarchy downwards, starting with PM2.

Now, let us see how HIERARCHYID helps to answer these questions. Let us first of all, recreate the Employees table having a HIERARCHYID column and then populate it. Here is the version of the insert statement that uses a HIERARCHYID.

INSERT INTO employees (EmployeeNode, EmployeeID, EmployeeName, ManagerID)
VALUES
 (CAST('/' AS HIERARCHYID), 1, 'CEO', 0),
 (CAST('/1/' AS HIERARCHYID), 2, 'PM1', 1),
 (CAST('/1/1/' AS HIERARCHYID), 4, 'TechLead1', 2),
 (CAST('/1/1/1/' AS HIERARCHYID), 6, 'Jacob', 4),
 (CAST('/1/1/2/' AS HIERARCHYID), 7, 'Steve', 4),
 (CAST('/1/2/' AS HIERARCHYID), 5, 'TechLead2', 2),
 (CAST('/1/2/1/' AS HIERARCHYID), 8, 'Bob', 5),
 (CAST('/1/2/2/' AS HIERARCHYID), 9, 'Mike', 5),
 (CAST('/2/' AS HIERARCHYID), 3, 'PM2', 1),
 (CAST('/2/1/' AS HIERARCHYID), 10, 'TechLead3', 3),
 (CAST('/2/1/1/' AS HIERARCHYID), 11, 'Laura', 10),
 (CAST('/2/1/2/' AS HIERARCHYID), 12, 'Jeff', 10),
 (CAST('/2/2/' AS HIERARCHYID), 13, 'TechLead4', 3),
 (CAST('/2/2/1/' AS HIERARCHYID), 14, 'Bill', 13),
 (CAST('/2/2/2/' AS HIERARCHYID), 15, 'Andrew', 13)

There are a few different ways to populate hierarchy information in a column with HIERARCHYID data type. We will discuss this in the next post. Let us now try to write the queries to answer the 3 questions we saw previously.

Question 1: Who are the bosses of TechLead4

DECLARE @h HIERARCHYID
SELECT @h = EmployeeNode FROM employees WHERE EmployeeName = 'TechLead4'
SELECT EmployeeName FROM employees WHERE @h.IsDescendantOf(EmployeeNode) = 1
 AND EmployeeNode <> @h
/*
EmployeeName
--------------------
CEO              
PM2   
*/

HIERARCHYID is implemented internally as a CLR data type and it has a number of methods. We will examine the methods exposed by HIERARCHYID in the next post. The method "IsDescendantOf()" returns 1 if it is the child of another HIERARCHYID value. The query first stores the HierarchyID of TechLead4 to a variable and calls the "IsDescendantOf()" method against all the rows in the table.

Question 2: Who are at the second level below PM2

DECLARE @h HIERARCHYID
SELECT @h = EmployeeNode FROM employees WHERE EmployeeName = 'PM2'
SELECT EmployeeName FROM employees WHERE EmployeeNode.GetAncestor(2) = @h
/*
EmployeeName
--------------------
Laura            
Jeff             
Bill             
Andrew    
*/

Another useful method is "GetAncestor()". This method returns true if a given value is the ancestor of another value by a given level. The above query stores the HierarchyID of 'PM2' to a variable. It then matches all records in the table and returns the rows with a HierarchyID value whose second level ancestor is 'PM2'.

Question 3: Who are the employees directly or indirectly reporting to PM2?

DECLARE @h HIERARCHYID
SELECT @h = EmployeeNode FROM employees WHERE EmployeeName = 'PM2'
SELECT EmployeeName FROM employees WHERE EmployeeNode.IsDescendantOf(@h) = 1
 AND EmployeeNode <> @h
/*
EmployeeName
--------------------
TechLead3        
Laura            
Jeff             
TechLead4        
Bill             
Andrew
*/

In the first example, we have seen the usage of "IsDescendantOf()". But the logic we used in the first example was different. In the first example, we matched all the rows against the variable and checked if a given row is the descendant of the variable. This example shows the reverse case. Here are we are testing if the variable is the descendent of any of the rows in the table.

HIERARCHYID has 10 methods and we just saw two of them. We will examine the other methods in the next posts.

SQL Server 2008 (KATMAI) TSQL Enhancements

1. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 1 - Variable initialization and Compound Assignment Operators
2. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 2 - Row Constructors
3. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 3 - New Data Type: DATE
4. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 4 - New Data Type: TIME
5. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 5 - New Data Type: DATETIME2
   
6. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 6 - DATETIMEOFFSET

SQL Server 2008 (KATMAI) TSQL Enhancements - Part 6 - DATETIMEOFFSET

In the previous few sessions we have discussed a few new data types introduced by SQL Server 2008. So far we have seen DATE, TIME and DATETIME2. In this session we will examine yet another new data type, DATETIMEOFFSET.

DATETIMEOFFSET

DATETIMEOFFSET stores a DATE and TIME value along with the TIMEZONE information. SQL Server 2005 had two DATETIME related data types: DATETIME and SMALLDATETIME. None of them was able to store time-zone information. SQL Server 2008 added a few more date/time related data types: DATE, TIME, DATETIME2 and DATETIMEOFFSET. Out of this, DATETIMEOFFSET is the only SQL Server data type that can store TIMEZONE aware date values. The following code snippet shows a basic example.

DECLARE @dto AS DATETIMEOFFSET
SELECT @dto = '2008-05-15 3:30:00 PM +05:30' -- IST
SELECT @dto
/*
----------------------------------
2008-05-15 15:30:00.0000000 +05:30
*/

A Use Case

DATETIMEOFFSET data type is very much helpful to handle date/time data that is collected from different locations falling into different time zones. There are many situations were you really need a time-zone aware date/time value. The following example shows a scenario where the DATETIMEOFFSET data type is very much useful.

Assume that we have a database that split into different servers located in different parts of the world. For the sake of this example, assume that we have 3 servers, in NY, India and Singapore. Suppose we have an auditing functionality which keeps track of the data modifications being done by the users. When something is modified, we log the name of the user and current time to an audit table.

Suppose the following sequence of events happen one morning.

1. User Bob modifies a record from Singapore at 11 AM
   
2. User Mike makes a modification at 11.30 AM from India
   
3. User Jacob makes another modification at 10 AM from NY
   

In the absence of a TIME-ZONE aware date column, we might get the following results.

DECLARE @t TABLE (UserName VARCHAR(20), ModificationTime DATETIME)
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Jacob','2008-05-15 10:00:00' -- NY
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Mike','2008-05-15 11:30:00' -- India
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Bob','2008-05-15 11:00:00' -- Singapore

SELECT * FROM @t ORDER BY ModificationTime
/*
UserName             ModificationTime
-------------------- -----------------------
Jacob                2008-05-15 10:00:00.000
Bob                  2008-05-15 11:00:00.000
Mike                 2008-05-15 11:30:00.000
*/

It is apparent that the results given above is wrong. User Jacob modified the record several hours after Mike modified it from India. Bob has modified the data before anyone else from Singapore. A time zone aware date/time column can make this much easier. Here is an example.

DECLARE @t TABLE (UserName VARCHAR(20), ModificationTime DATETIMEOFFSET)
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Jacob','2008-05-15 10:00:00 AM -05:30' -- NY
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Mike','2008-05-15 11:30:00 AM +05:30' -- India
INSERT INTO @t (UserName, ModificationTime)
 SELECT 'Bob','2008-05-15 11:00:00 AM +08:00' -- Singapore

SELECT * FROM @t ORDER BY ModificationTime
/*
UserName             ModificationTime
-------------------- ----------------------------------
Bob                  2008-05-15 11:00:00.0000000 +08:00
Mike                 2008-05-15 11:30:00.0000000 +05:30
Jacob                2008-05-15 10:00:00.0000000 -05:30
*/

This example clearly demonstrates a typical usage of the DATETIMEOFFSET data type. Let us have a closer look into the DATETIMEOFFSET data type in detail.

Range

DATETIMEOFFSET supports the same range as DATETIME2. It supports dates ranging from 1st January, 0001 through 31st December 9999. In addition, it supports time zone information between -14:00 and +14:00.

DECLARE @min AS DATETIMEOFFSET = '0001-01-01 00:00:00.0000000 -14:00'
DECLARE @max AS DATETIMEOFFSET = '9999-12-31 23:59:59.9999999 +14:00'
SELECT @min AS Min, @max AS Max
/*
Min                                Max
---------------------------------- ----------------------------------
0001-01-01 00:00:00.0000000 -14:00 9999-12-31 23:59:59.9999999 +14:00

Storage

Storage of DATETIMEOFFSET data types takes 8 to 10 bytes depending on the precision requested. When declaring a DATETIMEOFFSET variable, you can specify the required precision, just like DATETIME2.

DECLARE
 @dto AS DATETIMEOFFSET = '2008-07-02 22:49:06.3593750 +05:30',
 @dto0 AS DATETIMEOFFSET(0) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto1 AS DATETIMEOFFSET(1) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto2 AS DATETIMEOFFSET(2) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto3 AS DATETIMEOFFSET(3) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto4 AS DATETIMEOFFSET(4) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto5 AS DATETIMEOFFSET(5) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto6 AS DATETIMEOFFSET(6) = '2008-07-02 22:49:06.3593750 +05:30',
 @dto7 AS DATETIMEOFFSET(7) = '2008-07-02 22:49:06.3593750 +05:30'

SELECT
 @dto,   --2008-07-02 22:49:06.3593750 +05:30
 @dto0,  --2008-07-02 22:49:06 +05:30
 @dto1,  --2008-07-02 22:49:06.4 +05:30
 @dto2,  --2008-07-02 22:49:06.36 +05:30
 @dto3,  --2008-07-02 22:49:06.359 +05:30
 @dto4,  --2008-07-02 22:49:06.3594 +05:30
 @dto5,  --2008-07-02 22:49:06.35938 +05:30
 @dto6,  --2008-07-02 22:49:06.359375 +05:30
 @dto7   --2008-07-02 22:49:06.3593750 +05:30

SELECT
 DATALENGTH(@dto),  --10
 DATALENGTH(@dto0), --8
 DATALENGTH(@dto1), --8
 DATALENGTH(@dto2), --8
 DATALENGTH(@dto3), --9
 DATALENGTH(@dto4), --9
 DATALENGTH(@dto5), --10
 DATALENGTH(@dto6), --10
 DATALENGTH(@dto7)  --10

New Functions

The following new time-zone related functions were added that either operates on a DATETIMEOFFSET variable or returns a DATETIMEOFFSET data type.

SWITCHOFFSET()

This function modifies the offset value (time zone info) of a DATETIMEOFFSET value. The following snippet shows an example where the time-zone offset of a DATETIMEOFFSET value is changed using SWITCHOFFSET function.

DECLARE @dto AS DATETIMEOFFSET
SELECT @dto = '2008-07-02 22:49:06.3593750 +05:30' -- Indian Time
SELECT @dto AS IndianTime
/*
IndianTime
----------------------------------
2008-07-02 22:49:06.3593750 +05:30
*/
SELECT @dto = SWITCHOFFSET(@dto,'-05:00') -- Estern time
SELECT @dto AS EasternTime
/*
EasternTime
----------------------------------
2008-07-02 12:19:06.3593750 -05:00
*/

TODATETIMEOFFSET()

This function adds time zone information to a DATETIME variable/value and returns a DATETIMEOFFSET value. In the following example, '-5:30' is added to a DATETIME variable to obtain the DATETIME value as per Eastern Standard Time.

DECLARE @dt AS DATETIME = '2008-07-02 22:49:06'
SELECT @dt DateTime
/*
DateTime
-----------------------
2008-07-02 22:49:06.000
*/
DECLARE @dto AS DATETIMEOFFSET = TODATETIMEOFFSET(@dt, '-05:00')
SELECT @dto AS DateTimeOffset
/*
DateTimeOffset
----------------------------------
2008-07-02 22:49:06.0000000 -05:00
*/

SYSDATETIMEOFFSET()

This function returns a DATETIMEOFFSET value containing current date, time and time-zone. For example:

SELECT SYSDATETIMEOFFSET()
/*
----------------------------------
2008-07-02 23:25:30.5000000 +05:30
*/

Enhanced Functions

DATEPART()

The DATEPART() function takes two more time-zone related flags: TZ and TZOFFSET. TZ is the short name of TZOFFSET. It returns the time-difference of the given time zone in minutes. For example:

DECLARE @dt1 DATETIMEOFFSET = SYSDATETIMEOFFSET()
SELECT
 @dt1,                   -- returns 2008-07-03 22:37:56.8750000 +05:30                
 DATEPART(TZ, @dt1),     -- returns 330 (05:30 hours IST)
 DATEPART(TZOFFSET,@dt1) -- returns 330 (05:30 hours IST)

Other

ISDATE()

The ISDATE() function still does not recognize DATETIMEOFFSET values. I guess this will be fixed in the future releases. The following returns '0' indicating that the value is not a valid DATE value.

SELECT ISDATE('2008-07-02 22:49:06.3593750 +05:30')

Time Zone Names

DATETIMEOFFSET data types can store time zone information in terms of the time-difference from GMT but cannot store the time zone name (say, Indian Standard Time etc). The following post shows a workaround to find time zone name from a DATETIMEOFFSET value. http://blogs.msdn.com/mssqlisv/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

SQL Server 2008 (KATMAI) TSQL Enhancements

1. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 1 - Variable initialization and Compound Assignment Operators
2. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 2 - Row Constructors
3. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 3 - New Data Type: DATE
4. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 4 - New Data Type: TIME
5. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 5 - New Data Type: DATETIME2

Installing SQL Server 2008 on a Domain Controller

Amit has just made a post on installing SQL Server 2008 on a domain controller. You can find the post here: http://abwrites.blogspot.com/2008/07/installing-sql-server-on-domain.html

SQL Server 2008 (KATMAI) TSQL Enhancements

I had been writing a series of posts on SQL Server TSQL Enhancements. This post will serve as an index to all the posts in this series. I will keep this page updated when a new post is added in this series.

SQL Server 2008 (KATMAI) TSQL Enhancements

1. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 1 - Variable initialization and Compound Assignment Operators
2. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 2 - Row Constructors
3. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 3 - New Data Type: DATE
4. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 4 - New Data Type: TIME
5. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 5 - New Data Type: DATETIME2
6. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 6 - DATETIMEOFFSET

SQL Server 2008 (KATMAI) TSQL Enhancements - Part 5 - New Data Type: DATETIME2

We have seen a few new data types introduced by SQL Server 2008 in the previous posts. So far, we have examined DATE and TIME. DATETIME2 is yet another new data type that comes with SQL Server 2008.

SQL Server 2005 already has two date data types: SMALLDATETIME and DATETIME. The new data type, DATETIME2 supports a larger range of date values and has a precision up to 100 nanoseconds.

SMALLDATETIME

I am pretty sure that all of you have used this data type quite a lot. This is good for storing date/time values when we do not really need very high precision. SMALLDATETIME data type can store date values between January 1, 1900 and June 6, 2079. SMALLDATETIME values are accurate by one minute. It will ignore seconds up to 29.998 and rounds the value up to the next minute if it has 29.999 seconds or higher.

DECLARE @sdt AS SMALLDATETIME

SELECT @sdt = GETDATE()
SELECT @sdt
-- returns 2008-06-24 23:21:00

WAITFOR DELAY '00:00:10' -- wait for 10 SECONDS

SELECT @sdt = GETDATE()
SELECT @sdt
-- returns 2008-06-24 23:21:00 (even after waiting for 10 seconds)

SMALLDATETIME data type takes 4 bytes of storage. Internally, it is stored as two 2 BYTE integers, one storing the date part and the other, the time portion.

DATETIME

I think DATETIME is the DATE/TIME related data type that we use more often. I have seen people using this data type quite a lot, even when they do not really need to deal with date/time values outside the supported range of SMALLDATETIME data type. So, DATETIME seems to be more popular than SMALLDATETIME to many database developers.

DATETIME data type supports date/time values between January 1, 1753 and December 31, 9999. DATETIME values have an accuracy of around 3 milliseconds.

SELECT
CAST('2008-01-01 10:00:00.000' AS DATETIME), --2008-01-01 10:00:00.000
CAST('2008-01-01 10:00:00.001' AS DATETIME), --2008-01-01 10:00:00.000
CAST('2008-01-01 10:00:00.002' AS DATETIME), --2008-01-01 10:00:00.003
CAST('2008-01-01 10:00:00.003' AS DATETIME), --2008-01-01 10:00:00.003
CAST('2008-01-01 10:00:00.004' AS DATETIME), --2008-01-01 10:00:00.003
CAST('2008-01-01 10:00:00.005' AS DATETIME), --2008-01-01 10:00:00.007
CAST('2008-01-01 10:00:00.006' AS DATETIME), --2008-01-01 10:00:00.007
CAST('2008-01-01 10:00:00.007' AS DATETIME), --2008-01-01 10:00:00.007
CAST('2008-01-01 10:00:00.008' AS DATETIME), --2008-01-01 10:00:00.007
CAST('2008-01-01 10:00:00.009' AS DATETIME), --2008-01-01 10:00:00.010
CAST('2008-01-01 10:00:00.010' AS DATETIME)  --2008-01-01 10:00:00.010

Note that '9999-12-31 23:59:59.999' is not a valid DATETIME value because it will be rounded to the next millisecond and will result in January 1, 10000, which is well beyond the range a DATETIME value can support.

DATETIME data types takes 8 bytes of storage. It is stored internally as two 4 BYTE integers, one storing the DATE part and the other storing the TIME part.

DATETIME2

DATETIME2 is a new data type introduced by SQL Server 2008. It supports better precision (up to 100 nanoseconds) and supports a larger date range (between 1 January 0001 to 31 December 9999).

There had been some discussion about its name in the MSDN KATMAI forums. I agree that BIGDATETIME would have been much better. So SMALLDATETIME, DATETIME and BIGDATETIME falls in the same pattern as SMALLINT, INT and BIGINT and it really makes sense to me. Jamie has written a post on the same.

Precision

As mentioned earlier, DATETIME2 data type supports precision up to 100 nanoseconds. You can specify the level of precision that you need, by passing an optional argument (0 to 7). Here is an example:

SELECT
CAST(sysdatetime() AS DATETIME2),   --2008-06-24 23:57:53.6250000
CAST(sysdatetime() AS DATETIME2(0)),--2008-06-24 23:57:54
CAST(sysdatetime() AS DATETIME2(1)),--2008-06-24 23:57:53.6
CAST(sysdatetime() AS DATETIME2(2)),--2008-06-24 23:57:53.63
CAST(sysdatetime() AS DATETIME2(3)),--2008-06-24 23:57:53.625
CAST(sysdatetime() AS DATETIME2(4)),--2008-06-24 23:57:53.6250
CAST(sysdatetime() AS DATETIME2(5)),--2008-06-24 23:57:53.62500
CAST(sysdatetime() AS DATETIME2(6)),--2008-06-24 23:57:53.625000
CAST(sysdatetime() AS DATETIME2(7)) --2008-06-24 23:57:53.6250000

SYSDATETIME() is a new function introduced with SQL Server 2008. GETDATE() returns a DATETIME value and SYSDATETIME() returns a DATETIME2 value. Yet another new DATE/TIME function that comes with SQL Server 2008 is 'SYSUTCDATETIME()' which returns a DATETIME2 value with the current UTC time.

Storage

The storage requirements of DATETIME2 values vary based on the level of precision that you need. Values having more precision tend to use more storage space. The following example demonstrates that.

DECLARE
@dt1 AS DATETIME2 = SYSDATETIME(),
@dt2 AS DATETIME2(0) = SYSDATETIME(),
@dt3 AS DATETIME2(1) = SYSDATETIME(),
@dt4 AS DATETIME2(2) = SYSDATETIME(),
@dt5 AS DATETIME2(3) = SYSDATETIME(),
@dt6 AS DATETIME2(4) = SYSDATETIME(),
@dt7 AS DATETIME2(5) = SYSDATETIME(),
@dt8 AS DATETIME2(6) = SYSDATETIME(),
@dt9 AS DATETIME2(7) = SYSDATETIME()

SELECT
DATALENGTH(@dt1),--8 BYTES
DATALENGTH(@dt2),--6 BYTES
DATALENGTH(@dt3),--6 BYTES
DATALENGTH(@dt4),--6 BYTES
DATALENGTH(@dt5),--7 BYTES
DATALENGTH(@dt6),--7 BYTES
DATALENGTH(@dt7),--8 BYTES
DATALENGTH(@dt8),--8 BYTES
DATALENGTH(@dt9) --8 BYTES

ADO.NET Support

SQL Server 2008's DATETIME2 data type maps to ADO.NET DateTime data type. So your Visual Studio 2005 applications can pass DateTime values as arguments to stored procedures that take DATETIME2 data types and vice versa. Visual Studio 2008 has added SqlDbType.DateTime2 which is the ADO.NET equivalent of the SQL Server DATETIME2 data type. The following .NET data types maps to SQL Server 2008's DATETIME2 data type.

1. System.DateTime
   
2. System.Data.SqlDbType.DateTime2
   
3. System.Data.DbType.DateTime2
   

Enhancements to DATE/TIME Functions

The following DATE/TIME functions were enhanced with additional flags to support DATETIME2 values.

1. DATEDIFF()
   
2. DATEADD()
   
3. DATEPART()
   

let us see some examples.

DATEPART()

The following flags are added to the DATEPART() function.

1. ISO_WEEK / ISOWK / ISOWW
   
2. MCS / MICROSECOND
   
3. NANOSECOND / NS
   

The following example demonstrates all the flags supported by the DATEPART() function in SQL Server 2008.

DECLARE @d DATETIME2 = SYSDATETIME()
SELECT
@d,                         --2008-06-26 13:25:19.7656250
DATEPART(D, @d),            --26
DATEPART(DAY, @d),          --26
DATEPART(DD, @d),           --26
DATEPART(DAYOFYEAR, @d),    --178
DATEPART(DY, @d),           --178
DATEPART(DW, @d),           --5
DATEPART(W, @d),            --5
DATEPART(WEEKDAY, @d),      --5
DATEPART(HH, @d),           --13
DATEPART(HOUR, @d),         --13
DATEPART(ISO_WEEK, @d),     --26
DATEPART(ISOWK, @d),        --26
DATEPART(ISOWW, @d),        --26
DATEPART(WEEK, @d),         --26
DATEPART(WK, @d),           --26
DATEPART(WW, @d),           --26
DATEPART(M, @d),            --6
DATEPART(MM, @d),           --6
DATEPART(MONTH, @d),        --6
DATEPART(MCS, @d),          --765625
DATEPART(MICROSECOND, @d),  --765625
DATEPART(MINUTE, @d),       --25
DATEPART(MI, @d),           --25
DATEPART(MS, @d),           --765
DATEPART(MILLISECOND, @d),  --765
DATEPART(N, @d),            --25
DATEPART(NANOSECOND, @d),   --765625000
DATEPART(NS, @d),           --765625000
DATEPART(Q, @d),            --2
DATEPART(QQ, @d),           --2
DATEPART(QUARTER, @d),      --2
DATEPART(S, @d),            --19
DATEPART(SECOND, @d),       --19
DATEPART(SS, @d),           --19
DATEPART(Y, @d),            --178
DATEPART(YEAR, @d),         --2008
DATEPART(YY, @d),           --2008
DATEPART(YYYY, @d)          --2008

DATEADD()

The following flags were added to the DATEDIFF() function.

1. microsecond / mcs
   
2. nanosecond / ns
   

Let us look at an example that shows the various flags supported by the KATMAI version of DATEADD() function.

DECLARE @d DATETIME2 = SYSDATETIME()
SELECT
@d,                            --2008-06-26 14:41:53.5312500
DATEADD(year, 1, @d),          --2009-06-26 14:41:53.5312500
DATEADD(yy, 1, @d),            --2009-06-26 14:41:53.5312500
DATEADD(yyyy, 1, @d),          --2009-06-26 14:41:53.5312500
DATEADD(quarter, 1, @d),       --2008-09-26 14:41:53.5312500
DATEADD(qq, 1, @d),            --2008-09-26 14:41:53.5312500
DATEADD(q, 1, @d),             --2008-09-26 14:41:53.5312500
DATEADD(month, 1, @d),         --2008-07-26 14:41:53.5312500
DATEADD(mm, 1, @d),            --2008-07-26 14:41:53.5312500
DATEADD(m, 1, @d),             --2008-07-26 14:41:53.5312500
DATEADD(dayofyear, 1, @d),     --2008-06-27 14:41:53.5312500
DATEADD(dy, 1, @d),            --2008-06-27 14:41:53.5312500
DATEADD(y, 1, @d),             --2008-06-27 14:41:53.5312500
DATEADD(day, 1, @d),           --2008-06-27 14:41:53.5312500
DATEADD(dd, 1, @d),            --2008-06-27 14:41:53.5312500
DATEADD(d, 1, @d),             --2008-06-27 14:41:53.5312500
DATEADD(week, 1, @d),          --2008-07-03 14:41:53.5312500
DATEADD(wk, 1, @d),            --2008-07-03 14:41:53.5312500
DATEADD(ww, 1, @d),            --2008-07-03 14:41:53.5312500
DATEADD(weekday, 1, @d),       --2008-06-27 14:41:53.5312500
DATEADD(dw, 1, @d),            --2008-06-27 14:41:53.5312500
DATEADD(w, 1, @d),             --2008-06-27 14:41:53.5312500
DATEADD(hour, 1, @d),          --2008-06-26 15:41:53.5312500
DATEADD(hh, 1, @d),            --2008-06-26 15:41:53.5312500
DATEADD(minute, 1, @d),        --2008-06-26 14:42:53.5312500
DATEADD(mi, 1, @d),            --2008-06-26 14:42:53.5312500
DATEADD(n, 1, @d),             --2008-06-26 14:42:53.5312500
DATEADD(second, 5, @d),        --2008-06-26 14:41:58.5312500
DATEADD(ss, 5, @d),            --2008-06-26 14:41:58.5312500
DATEADD(s, 5, @d),             --2008-06-26 14:41:58.5312500
DATEADD(millisecond, 100, @d), --2008-06-26 14:41:53.6312500
DATEADD(ms, 100, @d),          --2008-06-26 14:41:53.6312500
DATEADD(microsecond, 1000, @d),--2008-06-26 14:41:53.5322500
DATEADD(mcs, 1000, @d),        --2008-06-26 14:41:53.5322500
DATEADD(nanosecond, 10000, @d),--2008-06-26 14:41:53.5312600
DATEADD(ns, 10000, @d)         --2008-06-26 14:41:53.5312600