SQL Server 2005/2008 Common Table Expression
Now this is something neat.
if you use many subset of data and subquery, your performance may hit. Select something,Increase something by 1,and so on.
Now sql server does support cte, kind like a temporary table can be referenced on your query but must be immidiately referenced.
Some Article said that it's much more light in performance .
I use it for the recursive query, like there's an id and parentid
and you need to concatinate id with the parentid.
So you need like a big loop recursive on your child node and the cte is perfect for this (Faster than UDF , Faster than Sub Query)
other benefits are very easy to program, you can set a limit of the recursive row.
;WITH expression_name [ ( column_name [,...n] ) ]
as
(
<anchor query>
union all
<recursive query join expression_name where parentid equals expression_name.id>
)
select <colum_list> from cte
<for xml> etc