Add a built-in table of numbers by Erland Sommarskog

There are several SQL problems that can be solved with a table of numbers.
This is a simple one-column table with numbers from 1 and up. It's easy to
construct and fill such a table. However, there are a couple of problems
with it:
1) If you query needs more numbers than there are in the table, you
get incorrect results.
2) Same thing happens if some smartass delets rows in the middle - or adds
a zero to the table.
3) There are queries where the optimizer does not know what the table is for
and makes incorrect assumptions.

The latter calls for some explanation. Assume this function:

CREATE FUNCTION inline_split_me(@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(SELECT ltrim(rtrim(convert(nvarchar(4000),
                 substring(@param, Number,
                            charindex(N',' COLLATE Slovenian_BIN2,
                                     @param + N',', Number) -
                            Number)
             ))) AS Value
     FROM Numbers
     WHERE Number <= convert(int, len(@param))
         AND substring(N',' + @param, Number, 1) = N',' COLLATE Slovenian_BIN2)

This function is used to convert a comma-separated list of numbers to a
table. If I say.

CREATE PROCEDURE get_company_names_inline @customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@customers) s ON C.CustomerID = s.Value
go
EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'

and have one million rows in my Numbers table, the optimizer thinks that
the Numbers table will return 300000 rows. Had Numbers been a
built-in table, the optimzier would have known the hit rate exactly.