Iteratively generate numeric values

I have a table, let's call it Foo, there is a field called Bar in the table, also, there is a field called LoremIpsum.

Bar is an nvarchar but I'm only interested in Bar values which are convertable to an integer number.

LoremIpsum is also a number.

I want to get the first n (value) numbers beginning with k from my Foo table where LoremIpsum has a specific value (LoremIpsum1) and (value) not in select Bar from Foo where LoremIpsum = LoremIpsum1

My input is: (LoremIpsum1, n, k)

where LoremIpsum1 is a particular value for LoremIpsum, n is the number of numbers to get and k is the offset.

My output should be a set of numbers which don't exist in the table as Bar values if LoremIpsum = LoremIpsum1. My current command:

WITH    q AS
        (
        SELECT convert(bigint, 50000) AS num
        UNION ALL
        SELECT  convert(bigint, num + 1)
        FROM    q
        )
SELECT  top 200 *
FROM    q
where convert(bigint, num) not in (select convert(bigint, Bar) from Foo where LoremIpsum = 68 and isnumeric(Bar + 'e0') = 1)
option (maxrecursion 365);

This query doesn't always work, at a computer the problem was that an nvarchar couldn't be converted to a bigint, probably it was a problem with the settings, the other error occurs if the needed recursion is deeper than the given maxrecursion.

In this case n = 200, k = 50000, LoremIpsum1 = 68

The main problem is that this query is recursive and I would like to modify it to be an iterative query.

Any help is greatly appreciated.

Best regards, Lajos 开发者_StackOverflow中文版Arpad.

You can use a permanent number table that has enough numbers for your need instead of the recursive cte.

create table NumberTable(Num int primary key)

You can use your recursive cte to populate the number table. Since it is just a one time thing the performance should not be that big deal and if it is you can have a look here.

WITH    q AS
        (
        SELECT 0 AS num
        UNION ALL
        SELECT  num + 1
        FROM    q
        )
insert into NumberTable(Num)        
select top 10000 num
from q
option (maxrecursion 0) 

Use the number table in your query with the offset.

declare @n int = 200
declare @k int = 50000

SELECT  top (@n) num+@k
FROM    NumberTable
--where convert(bigint, num+k) not in (select ...

The following view will yield up to 2^32 numbers, and could be extended to give more if necessary:

create view numbers AS
with
  n as (select 0 as n union select 1)
, nn as (select 0 as n from n as n1, n as n2, n as n3, n as n4)
, nnn as (select 0 as n from nn as n1, nn as n2, nn as n3, nn as n4)
, nnnn as (select 0 as n from nnn as n1, nnn as n2)
select row_number() over (order by n) AS n from nnnn

go

select n from numbers where n <= 50000

Regrettably there is a sort stage in the query plan, so it starts to slow down noticeably as n gets really large. However, on my desktop machine the slowdown does not really become apparent until generating around 1,000,000 rows.

You may see better performance if you work the exhibited strategy directly into your full query (rather than as a standalone view) as the number of rows that need to be sorted would reduced to the minimum.