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.

Peter de Heeron 3/16/2016 at 12:15 AMIt also opens up ways for SQL Server to generate better plans as it will know the intention of the code.

As an addition suggestion, please allow for an optional step argument.

It would be nice to be able to express well the generation of even numbers from 0 to 10, without additional T-SQL.

Please allow for counting down as well as up, everything that removes complexity from T-SQL is a desirable trait.

Personally I made these kind of functions before in .NET and made sure small mistakes do not result in endless looping to make sure start/stop (form/to) values must align with the step direction.