Factorial in SQL

Example for versions Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012

This example uses recursive factorial definition, implemented as a recursive query — a feature included in SQL Server 2005 and higher. Each row of recursive query contains two numeric fields — n and f = n!, and each row is calculated using the data of the previous row.

Note that starting with 2008 version you can calculate factorials only up to 12!. Trying to calculate 13! results in “Data truncation” error, i.e. the resulting value is too large for its datatype.

with factorial(n, f) as
 select 0, 1
  union all
 select n+1, f*(n+1) from factorial where n<16
select cast(n as varchar)+'! = '+cast(f as varchar)
  from factorial