Hi all,

I wrote this for fun, and thought I'd share it here, since it's vaguely related. It's interesting mathematics (in that the method works) if nothing else.

If you set @i below to your set size, then a list of all combinations of numbers is returned (as a varchar). e.g. @i = 3 gives:

210

201

120

021

102

012

The timings on my pc are:

Size  Seconds  Rows

7     0        5040

8     1        40320

9     6        362880

10    60       3628800

--This SQL script is safe to run

--Inputs

DECLARE @i TINYINT

SET @i = 7 --set size

--Validation

IF @i > 10 BEGIN PRINT 'i is too large' SET @i = 0 END

--Declarations

CREATE TABLE #t (n TINYINT, v VARCHAR(10))

CREATE CLUSTERED INDEX #ix_t ON #t (n)

DECLARE @n TABLE (i TINYINT) --numbers table

DECLARE @Counter INT

--Initialisations

INSERT @n SELECT 0

INSERT #t SELECT 0, '0 '

SET @Counter = 1

--Loop for each integer from 1 to @i-1

WHILE @Counter <= @i - 1

BEGIN

    INSERT @n SELECT @Counter

    INSERT #t SELECT @Counter, STUFF(v, i+1, 0, @Counter)

              FROM #t, @n WHERE n = @Counter - 1

    SET @Counter = @Counter + 1

END

--Select results we're interested in

SELECT v FROM #t WHERE n = @i - 1

--Tidy up

DROP TABLE #t