I had a fairly straightforward SQL problem to solve the other day. Given a child record, go find the top most parent (i.e. it’s parent, and then go find its parent until you hit the top most record). This is a typical data design pattern if you ever had a table of employees and wanted to create an organisational structure diagram.
One can “walk the tree” within T-SQL by recursively calling a stored procedure and passing in an identifier, and keep looping until you basically hit a NULL field value with the identifier column. However, what appears to be a more performant solution and one that’s definetly more elegant is to use Common Table Expressions available with SQL Server 2005 and up.
MSDN has a straightforward article on how to use Common Table Expressions so if you ever have to need to do recursive queries in SQL Server definetly check it out.
Tuesday, Feb 8, 2011