For anyone that's interested in these rCTEs and because there's been a recent flurry of possible uses appear in the forums, I took another look trying to improve the speed.

I tried various combinations but the one that seemed to be the best was this one (UNIQUEnTuples).

;WITH UNIQUEnTuples (n, Tuples, ID) AS (

SELECT DISTINCT 1, CAST(strcol AS VARCHAR(8000)), strcol

FROM @t

UNION ALL

SELECT 1 + n.n, t.strcol + ',' + n.Tuples, strcol

FROM UNIQUEnTuples n

CROSS APPLY (

SELECT strcol

FROM @t t

WHERE t.strcol < n.ID) t

)

SELECT *

FROM UNIQUEnTuples

ORDER BY n, Tuples

I used 18 unique n-Tuples which generates 262,143 rows (combinations).

Using a temp table instead of a table variable and dumping the output into holding variables, time improvements (averaged over 5 runs) were:

- CPU: 44%

- Elapsed: 45% Edit: Corrected

Removing DISTINCT of course (if you can get away with it) measurably improved the speed as well.

Changes:

- The Tuples column was changed to VARCHAR(8000) on the assumption that you probably aren't working with so many combinations (and your IDs are short enough) that you need 2GB of storage for them.

- Switched the order of tables accessed in the recursive leg and used a CROSS APPLY instead of a JOIN.

- Added the ID column to the rCTE, to allow checking against a much shorter length string (or if your ID is an INT it would then switch to an INT comparison).

- Removed the CHARINDEX check because it was not needed.

I verified each of these improvements incrementally so each had some effect. Presumably, similar changes to the nTuples rCTE would have similar performance impacts.