


There are several SQL problems that can be solved with a table of numbers. This is a simple onecolumn 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 commaseparated 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 builtin table, the optimzier would have known the hit rate exactly.
While this would be a nice backport, I would argue this is fairly minor work for SQL Server 2014+ (in an SP) and giving us a table with an integer's worth of numbers, would be very handy. I would suggest that the table be a DMV visible in every database.