Tech Off Post

Single Post Permalink

View Thread: Hierarchical Recursion in T-SQL92
  • User profile image

    The thing to avoid above all else is round-tripping to the server. This means that you should get all the data you need in one hit, not call the SQL Server in multiple requests for each level descended.

    SQL Server 2005 has Common Table Expressions which have a major role in reducing the complexity of T-SQL recursion.

    Basically, when outputting all the descendents of a node A, there are two logical parts to the query, whether or not you choose to use common table expressions:

    1) Seed a temporary table with the record/s you wish to find the descendents thereof;

    2) Identify the descendents and add to the table, looping as you go (not needed with SQL 2005 CTEs).

    For SQL 2000, basically, create a temporary table, or better still, a table variable with a column/s for the primary keys only and another column for the level.

    Add the root node/s.

    For SQL 2000, in a loop using while @@rowcount > 0, insert rows with an incrementing level number, joining the temporary table PKs against the source table's ParentPK/s where level = level -1.

    Finally, when your table is full of values, join against the source table or others as appropriate to get the data you require out.