By the way, do we have any solution to improve performance of CTE in case the category tree has depth > 4?
As I knew, if we use CTE to do recursive algorithm, SQL engine must read so many times. In my case, there are ~4000 categories and max of depth = 4, and I run CTE
Table 'Category'. Scan count 2, logical reads 2543467, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 27561, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Thanks,