November 2010 (1)
August 2010 (1)
July 2010 (1)
June 2010 (3)
July 2009 (3)
June 2009 (1)
May 2009 (1)
February 2009 (1)
January 2009 (1)
November 2008 (3)
October 2008 (4)
September 2008 (9)
August 2008 (6)
July 2008 (3)
June 2008 (3)
January 2008 (1)
November 2007 (2)
October 2007 (6)
September 2007 (5)
August 2007 (22)
July 2007 (6)
June 2007 (1)
May 2007 (3)
April 2007 (27)
March 2007 (8)
February 2007 (6)
September 2006 (2)
August 2006 (4)
July 2006 (9)
June 2006 (17)
May 2006 (20)
April 2006 (12)
March 2006 (9)
February 2006 (4)
January 2006 (3)
December 2005 (2)
November 2005 (4)
October 2005 (5)
September 2005 (37)
August 2005 (83)
July 2005 (6)

Active Directory / LDAP (0)
ASP.Net (19)
Blackberry Development (4)
c# (34)
c++ (3)
Code Camp (1)
Excel (1)
Exchange (3)
Front Page 2003 (6)
FTP User Editor (4)
HTML / CSS / DHTML (8)
IIS (146)
IIS - Log Parser (7)
IIS / FTP (12)
IIS / Tools / Administration (42)
IIS / Tools / Authentication (6)
IIS / Tools / Compression (8)
IIS / Tools / Crash & Hang (12)
IIS / Tools / ISAPI Filters (17)
IIS / Tools / Log Files (17)
IIS / Tools / Scripts (28)
IIS / Tools / Security (9)
IIS / Tools / SSL (6)
IIS 7 (3)
Internet Information Server (1)
Me (Chris Crowe) (6)
MIME Types (1)
Misc (72)
Oulook Express (2)
Silverlight (1)
SQL Server (27)
SQL Server CTE (1)
Vista (15)
Vista Gadgets (8)
Visual Studio (11)
Voice over BroadBand (1)
Windows (33)
Windows Powershell (3)
Windows Sharepoint Services (0)
Windows Sharepoint Services (15)
Windows Vista (14)
Wine Cellar (1)
WMI (8)
SQL Server CTE (1) blog.crowe.co.nz.Models.Category

SQL Server CTE

Microsoft SQL Server 2005 - CTE Example of a simple hierarchy

I have a simple table here which shows a list of devices and each device can have a parent device except for the top item which has a <NULL> value for the ParentID column.

Using SQL 2005 CTE (Common Table Expression) to render this full hierarchy we could use a simple query such as this:

What this CTE is doing is allowing us to run a hierarchical query.

We first off find the root of our tree, which in this case is referenced by a NULL value in the ParentID column.

SELECT ParentID, ID,0, [DeviceType], cast(DeviceType as nvarchar(1024))
FROM DeviceType
WHERE ParentID IS null

This technically returns the same details as row #1 in the above graphic.

What the UNION ALL does is executes a query with a join against the CTE virtual table.

So we technically are sort of doing this:

Create Virtual Table with the following fields:

  • ParentID
  • ID,
  • Level,
  • DevieType,
  • Sort

This line inserts one row into the virtual table, the ID in this case will be 1 because that was the ID in our DeviceType table of the record with the ParentID of Null.

SELECT ParentID, ID,0, [DeviceType], cast(DeviceType as nvarchar(1024))
FROM DeviceType
WHERE ParentID IS null

Now we run the following query on the virtual table until every row is processed in the virtual table.

Note: This select is really like an insert into the virtual table. each row returned in the select is appended to our virtual table.

SELECT dt.ParentID, dt.ID, level+1, dt.DeviceType, cast(Sort + '|'+dt.Devicetype as nvarchar(1024))
FROM DeviceType dt INNER JOIN hierarchy h ON dt.ParentID = h.ID

So the first time it is run we see that h.ID would be equal to 1 which was the ID of the record with ParentID of Null. If there was any records matching this select statement they are added to the virtual table and this continues until all of the virtual table rows have been processed.

At the end of it we have a Virtual table that we can then do what we want with such as join it to another table or just return the data, in our case we run this sql statement.

select ID, ParentID, DeviceType, LEVEL, Sort
from Hierarchy
order by sort

But what happens if I have an record and want to find the parent hierarchy for it?

The following query will allow us to return the parent hierarchy for the item Virtual Server Hosts which has an ID of 10.

Note: There are problems with the results of this query!

Can you see the problem with the results?

We have correctly returned the parents starting from the Virtual Server Hosts and walked up the tree until we get to the root node. But if you look at the Level field it is not actually containing the correct value as the Virtual Server Hosts fields should be at level 3 and not at level 0.

Why is this?

Well we can't determine at what level the Virtual Server Hosts is at when we start creating the virtual table so we just hard code it to 0. 

The problem also is that if you wanted to render this out to say a web browser using the following hierarchy we would have to navigate through our records backwards.

  • Object
    • Server
      • Virtual Servers
        • Virtual Server Hosts

How to fix this!

What we can do in this case is run a second CTE joined to the first CTE because each CTE is just like a virtual table. When the first CTE has completed processing we are basically left with a table with records in it like below.

Our second CTE now just references the records in the first CTE as if it was a standard table.

As you can see in the query in the above graphic our second CTE starts by looking for a record which has the ParentID being set to NULL.

This will return our real parent as the first record to be inserted into our second virtual table. 

Now we run the following query on the second virtual table until every row is processed in the virtual table. Like the first virtual table, the select appends records to our virtual table and these records get processed until all records have been processed.

SELECT h.ParentID, h.ID, hr.LEVEL+1, h.DeviceType, cast(hr.Sort + '|'+h.sort as nvarchar(1024))
FROM Hierarchy h INNER JOIN HierarchyReverse hr ON h.ParentID = hr.ID

We have changed the join in the second CTE to join from the ParentID --> ItemID, which is the opposite of the first CTE which joined the ItemID --> ParentID. This allows us to navigate down the tree instead of up the tree.

Confusing? hopefully not....

What we end up with is this:

Now you may be wondering what this Sort column is and why we have it at all?

The reason is that we need to have something that is sortable, using the DeviceType is not since it would corrupt our hierarchy by giving us A-Z, the same goes for level, 0-9

What we do is we create a field that contains the hierarchy so far and then add the DeviceType to the end of it. This makes a sortable field that will not corrupt our hierarchy but give us A-Z sorting within each level of the hierarchy.

Notice the problem with this?

Well I have made a decision that all of my hierarchies of DeviceType will not exceed 1024 characters by casting the values with the statement : NVARCHAR(1024)

Well we could use a NVARCHAR(MAX) and the problem is resolved, but this is dependant on your knowledge of the application.

Hopefully this has shown you and me how the CTE's work while they are running - well it looks like that to me and it makes me understand it a bit easier as they can be hard to picture sometimes.