I've been helping my client move to new technologies (VS2005, Team System, EntLib2). Unfortunately, SQL Server 2005 isn't yet in the road map. The pilot application has the typical Employee hierarchy. In SQL Server 2005, recursive queries are a breeze using CTEs once you get the hang of it...but they're not available in SQL SERVER 2000. To approximate recursive queries, one uses Table-valued User-defined Functions (TVF) in SQL Server 2000. Unfortunately, it's impossible to use anything but a constant as the parameters in SQL Server 2000. To get around this limitation, SQL Server 2005 now has the APPLY query operator.
I call this double jeopardy!
More info on CTEs, TVFs and APPLY operator for SQL Server Magazine (subscription required)