jlennartz (9/27/2010)
The discussion usually answer my questions but not this time. Doesn't "isnumeric(char(32)) as n" return 0? And if so wouldn't "select sum(n) from l" also equal 0? I guess I don't see how the sum of n got to 16 if n is alway 0?
The start of the query is a "recursive CTE". It consists of an anchor query and a recursive query. The anchor query is:
select 32 as i, char(32) as s, isnumeric(char(32)) as n
This returns a single row, with columns 1 (integer value 32), s (char(1), value ' ' (the space character)), and column n (integer value 0, the result of ISNUMERIC(char(32))).
The recursive query is:
select i+1, char(i+1), isnumeric(char(i+1)) from l where i < 127
Note that the FROM clause references the CTE itself. That makes the CTE recursive. On the first iteration, the FROM denotes the reqults of the anchor query. Since i = 32 in that row, the recursive part will now produce a similar row for the value i+1 (33). But then the recursing starts. The recursive part is evaluated again, with this new row as input, so now a row is produced that starts with i = 33 and generates column values for the value 34. This continues until the WHERE clause is no longer satisfied.
[The actual execution plan will probably be more efficient than this!]
The result is a table with 96 rows, with i ranging from 32 to 127 and s and n representing char(i) and isnumeri(char(i)) for each row.
The outer query then sums those isnumeric values.