Sometimes a client wants to know why they shouldn’t just stick with Sql Server 2000. There are many compelling reasons to use Sql Server 2005 or 2008 over 2000, but for today I’ll focus on just one: Recursive Joins.
One criticism of SQL Server years ago when I first started using it was that it didn’t have Oracle’s “connect by” feature, which let you do recursive joins, which basically let you do tree based queries in SQL. You have a table with a “parentid” column, and with a recursive join you can walk up or down the tree and get all the descendents or all the parents of an object.
Addressing this need, SQL Server added some recursive join features in 2005 and 2008. It can be hard to google because “sql server connect by” has hundreds of pages saying “can’t be done in sql server” and pointing to the “sql for smarties” nested sets solution (which uses triggers to maintain extra sorting rows to make querying trees possible).
“with” was added in 2005 as the syntax for working with “Common Table Expressions”. See http://consultingblogs.emc.com/christianwade/archive/2004/11/09/234.aspx
The basic idea is you establish a base to work from with a simple select, then union in the children (or parents or whatever) of the base, and your subquery in the union can refer back to the base as whatever you named it in the ‘with’.
2008 takes it one step further and lets you store the hierarchy path in the data model with HierarchyID. http://msdn.microsoft.com/en-us/magazine/cc794278.aspx This makes the data model a little less obvious to work with but looks like a good way to go if you’re developing a new data model. I happen to be working with an existing tree table so I’m sticking to ‘with’ for now.
An example “with” query to show all of the not-deleted category tree in ASPDotNetStorefront (intentionally including unpublished categories, but you could easily exclude them):
with c as (
select cast('/'+name as varchar(1000)) as pth,categoryid,displayorder,name,0 as lvl
from dbo.Category c1
where c1.ParentCategoryID=0 and Deleted=0
union all
select cast(c.pth+'/'+c1.name) as varchar(1000)) as pth, c1.categoryid, c1.displayorder, c1.name, lvl+1 as lvl
from dbo.category c1 join c on c1.ParentCategoryID = c.CategoryID
where c1.Deleted=0
)
select lvl, c.pth
from c join dbo.category c1 on c.categoryid = c1.categoryid
order by lvl,c.displayorder,c.pth;
