Recursion and FOR XML
Kent just published an example on how to get recursive XML structures (think employee-manager). The new version of the FOR XML whitepaper actually contains a section with such a sample (I need to get the MSDN team to publish it). I don't want to spoil the content of the whitepaper here, but I would like to add some comments to Kent's approach (I am sending some comments on minor coding issues to Kent directly).
First, there is a recursion limit of 32 for executing recursive functions in SQL Server. This should not be a problem for most hierarchies, but somebody is bound to run into it.
Secondly, since SQL is a declarative language, there is no guarantee that the select clause (the projection) gets executed after the where clause (the filter). Thus, Kent's query may end up in an endless recursion (limited by the nesting level mentioned above, fortunately :-)) if the projection clause gets executed before the filter is applied. In the whitepaper, we will use a CASE statement around the recursive function invocation. This will make sure that the query execution does not calculate the function recursively if the optimizer decides to apply the filter after executing the projection.