### Description

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.

Details
Proposed Solution

Add a built-in table of numbers is able to return positive numbers from 1 to 2^31-1. Presumably it would be a table

function. Either it would be parameterless, and you would use the WHERE clause to specify which numbers you need. But it could also have two parameters specify the start and end of your desired range.

A built-in table of numbers would remove the robustness problems with a regular table, and the optimizer would be

able to use the information in a smarter way.

Benefits

Improved Reliability

Improved Performance

Cade Rouxon 3/19/2013 at 1:14 PM