title: Generate all combinations in SQL
body: <p>I need to generate all combinations of size <code>@k</code> in a given set of size <code>@n</code>. Can someone please review the following SQL and determine first if the following logic is returning the expected results, and second if is there a better way?</p>
  
  <pre><code>/*CREATE FUNCTION dbo.Factorial ( @x int ) 
  RETURNS int 
  AS
  BEGIN
      DECLARE @value int
  
      IF @x &amp;lt;= 1
          SET @value = 1
      ELSE
          SET @value = @x * dbo.Factorial( @x - 1 )
  
      RETURN @value
  END
  GO*/
  SET NOCOUNT ON;
  DECLARE @k int = 5, @n int;
  DECLARE @set table ( [value] varchar(24) );
  DECLARE @com table ( [index] int );
  
  INSERT @set VALUES ('1'),('2'),('3'),('4'),('5'),('6');
  
  SELECT @n = COUNT(*) FROM @set;
  
  DECLARE @combinations int = dbo.Factorial(@n) / (dbo.Factorial(@k) * dbo.Factorial(@n - @k));
  
  PRINT CAST(@combinations as varchar(max)) + ' combinations';
  
  DECLARE @index int = 1;
  
  WHILE @index &amp;lt;= @combinations
  BEGIN
      INSERT @com VALUES (@index)
      SET @index = @index + 1
  END;
  
  WITH [set] as (
      SELECT 
          [value], 
          ROW_NUMBER() OVER ( ORDER BY [value] ) as [index]
      FROM @set
  )
  SELECT 
      [values].[value], 
      [index].[index] as [combination]
  FROM [set] [values]
  CROSS JOIN @com [index]
  WHERE ([index].[index] + [values].[index] - 1) % (@n) BETWEEN 1 AND @k
  ORDER BY
      [index].[index];
  </code></pre>