Friday, October 31, 2003 2:19 PM nielsb

T-SQL Enhancements in Yukon


By reading mine and other bloggers entries about Yukon you may come to the conclusion that the big enhancements in Yukon are the inclusion of the CLR, XML and Web Services and the Service Broker. Yes, thay are big enhancements and very exciting, but...

T-SQL has been greatly enhanced as well, and one of my favorite new features is the Common Table Expression; the CTE. A CTE is a temporary named result set or view, and a CTE can reference itself, in which case it's called a recursive query. In other words, the CTE allows you to execute recursive (hierarchical) queries in a really easy way.

Assume you have a table looking something like so:

create table emps (
  id int identity primary key,
  [name] varchar(15),
  mgrid int)

The table above is self-referencing and holds employee names with an additional column (mgrid) pointing to who the immediate manager is (the id column). By inserting some data in the table:

insert into emps values('Don', null)
insert into emps values('Bob', null)
insert into emps values('Niels', 1)
insert into emps values('Keith', 2)
insert into emps values('John', 4)
insert into emps values('Steve', 1)
insert into emps values('Tim', 2)

we can see that we get a hierarchical relationship with 'Niels' and 'Steve' reporting to 'Don'

  • Don
    • Niels
    • Steve

and the chain for Bob looks something like this:

  • Bob
    • Keith
      • John
    • Tim

Trying to create a SQL statement wich retrieves the relation ship for - for example - Bob, would in previous versions of SQL Server be somewhat hard, as we do not really know the depth of the graph. We would probably resort to cursors and loop the resultsets etc.

In Yukon however we can use a self referencing CTE:

with emph(id, name,mgrid) as ( 
-- this is the anchor member
select id, name,mgrid from emps
where id = 2

union all
-- this is the recursive member
select e.id, e.name,e.mgrid from
emps e
join emph eh
on e.mgrid = eh.id
)
select * from emph
order by id, manid

The with clause defines the CTE, which is followed by something caled the anchor member. The anchor member defines the "root" of the query and is followed by a union all query which is the recursive member. The recursive member executes a recursive query until no more results are available. At that stage we can then retrieve the result. Simple or what?!


Comments

No Comments