2006-07-21

Auxiliary roundup - The SQL table valued for loop function?

After creating the numbers CTE in my last post I wanted to go on building my standard auxiliary schema. First of all lets create the schema. Quite simple:


CREATE SCHEMA [Auxiliary]


A schema in SQL server 2005 is a collection of database entities that form a single namespace.
I now got a place to put auxiliary entities.

First off, I decided to put my Numbers CTE into a table valued function. I wanted to make it a bit flexible by giving the startnumber, endnumber and increment as parameters.
Incidentally this suddenly looked a little bit like the For(i=1; i <=1000; i++) construct in C and C#


CREATE FUNCTION Auxiliary.Numbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS @RetNumbers TABLE
(
[Number] int PRIMARY KEY NOT NULL
)

AS
BEGIN
WITH
Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END;


To use this to retrieve a list of numbers between 10 and 1000 incremented by 10:


SELECT Number FROM Auxiliary.Numbers(10,1000,10)


This will return:


Number
-----------
10
20
30
...
980
990
1000

(100 row(s) affected)


For a large number of numbers this function is quite slow. An inlined version of this performs much better, but you need to specify the MAXRECURSION hint in the statement using the function:


CREATE FUNCTION Auxiliary.iNumbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS TABLE
AS
RETURN
(
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
SELECT n AS Number from Numbers
)


To use this to retrieve a list of numbers between 10 and 1000 incremented by 10:


SELECT Number FROM Auxiliary.iNumbers(10,1000,10)


If your "table" will return more than 101 numbers (100 recursions):


SELECT Number FROM Auxiliary.iNumbers(1,10000,1)
OPTION(MAXRECURSION 10000)


If you need really many numbers then you definitely should build a permanent numberstable.
Louis Davidson wrote a nice post a about it here.

3 comments:

Anonymous said...

[b][url=http://cheap-car-insurance.quickfreehost.com] free online car insurance quote [/url][/b]
[b][url=http://seoguide.ws] churchil
l car insurance [/url][/b]
[b][url=http://nutritionguide.ws] car insurance cost [/url][/b]
[b][url=http://affiliaterevenueguide.ws] teen car insurance [/url][/b]
[b][url=http://careeremploymentguide.com] infinity car insurance [/url][/b]
[b][url=http://fitnessequipmentguide.net] norwich union car insurance [/url][/b]
[b][url=http://inetmarketingguide.ws] rental car insurance [/url][/b]
[b][url=http://interiordecorguide.ws] general car insurance [/url][/b]
[b][url=http://landscapingguide.ws] online car insurance [/url][/b]
[b][url=http://mortgageguide.ws] progressive car insurance quote [/url][/b]
[b][url=http://personalfinanceguide.ws] direct car insurance [/url][/b]
[b][url=http://trafficbuildingguide.net] progressive car insurance quote [/url][/b]
[b][url=http://vacationrentalsguide.ws] usaa car insurance [/url][/b]
[b][url=http://wealthbuildingguide.net] car insurance san diego [/url][/b]
[b][url=http://progressive-car-insurance.wealthbuildingguide.net] buy car insurance online [/url][/b]
[b][url=http://compare-car-insurance.vacationrentalsguide.ws] nationwide car insurance [/url][/b]
[b][url=http://cheapest-car-insurance.trafficbuildingguide.net] in car insurance [/url][/b]
[b][url=http://classic-car-insurance.personalfinanceguide.ws] online car insurance rate [/url][/b]
[b][url=http://free-car-insurance-quote.mortgageguide.ws] norwich union car insurance [/url][/b]
[b][url=http://cheap-car-insurance-quote.landscapingguide.ws] general car insurance [/url][/b]
[b][url=http://low-cost-car-insurance.interiordecorguide.ws] instant car insurance quote [/url][/b]
[b][url=http://car-insurance-quote.inetmarketingguide.ws] in car insurance [/url][/b]
[b][url=http://car-insurance-rates.homeimprovementguide.ws] progressive car insurance [/url][/b]
[b][url=http://new-york-insurance.fitnessequipmentguide.net] tesco car insurance [/url][/b]
[b][url=http://california-car-insurance.estateguide.ws] low cost car insurance [/url][/b]
[b][url=http://car-insurance-rate.careeremploymentguide.com] cheap online car insurance quote [/url][/b]
[b][url=http://auto-insurance-company.affiliaterevenueguide.ws] fresno car insurance [/url][/b]
[b][url=http://online-car-insurance.acneguide.ws] cheap car insurance quote uk [/url][/b]
[b][url=http://auto-cheap-insurance.nutritionguide.ws] car insurance rating [/url][/b]
[b][url=http://auto-insurance-quote.seoguide.ws] car insurance n [/url][/b]

http://cheap-car-insurance.quickfreehost.com

Random Keyword: :)
[b]car insurance comparison[/b]

Anonymous said...

Hello! 

I need help with my crappy computer! It is always freezing when i open IE and going to MSN? What do you think?

By the way, I love that too!  Where did you get that at?  

Bye, - Shelia! 



[color=#6699cc][url=http://free-paid-surveysmalnyj.blogspot.com]how I make money with paid surveys[/url][/color]

Anonymous said...

Good day! 

what is your favorite color of....mine is blue!

By the way, I love that too!  How did you find that?  

See you soon! Girly Girl 


[color=#6699cc][url=http://canada-paid-surveymelbqqvy.blogspot.com]see how I make free money with paid online surveys[/url][/color]