 # SQL Server Stirling Number of the Second Kind function

STIRLING2
Updated: 21 July 2017

Use the SQL Server scalar function STIRLING2 to calculate the Stirling numbers of the second kind. The Stirling numbers of the second kind count the number of ways to partition n elements into k nonempty sets: Where is the binomial coefficient (see BICO).

Stirling numbers of the second kind exhibit the following recursive relationship. Syntax
SELECT [wct].[STIRLING2] (
<@n, int,>
,<@k, int,>)
Arguments
 Input Name Definition @n Number of items @k Number chosen
Return Type
float
Remarks
• If @n <= 0 then NULL is returned.
• If @k < 0 then NULL is returned.
• If @k > n then NULL is returned.
• S(0,0) = 1
• S(n,0) = 0
• Available in XLeratorDB / statistics 2008 only
Examples
Example #1

Let’s say that we have a fair die and we roll it 10 times. We can use the Stirling numbers of the second kind to predict the probability that the result set will contain 1, 2, 3, 4, 5 or 6 distinct values.

SELECT
k.SeriesValue as [N],
wct.BICO(6,k.seriesvalue)*
wct.STIRLING2(10,k.seriesvalue)*
wct.FACT(k.SeriesValue)*
wct.POWER(6,-10) as p
FROM
wct.Seriesint(1,6,NULL,NULL,NULL)k

This produces the following result.

Example #2

A lower triangular representation of the first few Stirling numbers of the second kind.

SELECT ,,,,,,,,,
FROM (
SELECT
n.SeriesValue as n
,k.seriesValue as k
,cast(wct.STIRLING2(n.SeriesValue,k.SeriesValue) as int) as S1
FROM
wct.SeriesInt(1,10,NULL,NULL,NULL)n
CROSS APPLY
wct.SeriesInt(1,n.seriesvalue,NULL,NULL,NULL)k
)d
PIVOT (MAX(S1) FOR k IN (,,,,,,,,,))pvt
ORDER BY
n

This produces the following result.  Copyright 2008-2023 Westclintech LLC         Privacy Policy        Terms of Service