Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Numbers Table


The Numbers Table

Author
Message
CheeseMan316
CheeseMan316
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 71
Comments posted to this topic are about the item The Numbers Table
MrAkki
MrAkki
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 467
Here is another solution without using loops....


INSERT INTO #util_nums
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.[object_id])
FROM sys.columns a
CROSS JOIN sys.columns b
GO
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45382 Visits: 39938
Just an FYI... the source of the CTE in your article is by a fellow called Itzik Ben-Gan and can be found at the bottom of page 255 in a book titled "Inside Microsoft SQL Server 2005 - T-SQL Querying".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
yuvalpe
yuvalpe
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 15
This topic was cover complitly by Itzik Ben-Gan years ago
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6281 Visits: 1407
Earlier coverage was more than this. But this one also is good. Smile



Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1836
I'd consider using a table valued function instead that only returns the rows I'm interested in:

e.g.:
CREATE FUNCTION [dbo].[Nums]
(
@StartNum int,
@EndNum int
)
RETURNS @table TABLE (Num int)
AS BEGIN

DECLARE @tmpNum int
SET @tmpNum = @StartNum
WHILE @tmpNum < @EndNum
BEGIN
INSERT INTO @table VALUES (@tmpNum)
SET @tmpNum = @tmpNum + 1
END

RETURN
END
GO

Example Usage:
SELECT * FROM Nums(1000, 2000)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45382 Visits: 39938
jacroberts (11/24/2008)
I'd consider using a table valued function instead that only returns the rows I'm interested in:

e.g.:
CREATE FUNCTION [dbo].[Nums]
(
@StartNum int,
@EndNum int
)
RETURNS @table TABLE (Num int)
AS BEGIN

DECLARE @tmpNum int
SET @tmpNum = @StartNum
WHILE @tmpNum < @EndNum
BEGIN
INSERT INTO @table VALUES (@tmpNum)
SET @tmpNum = @tmpNum + 1
END

RETURN
END
GO

Example Usage:
SELECT * FROM Nums(1000, 2000)


Ummm... have you actually tried using one with a WHILE loop like that? A cross-joined CTE will be much quicker. The whole purpose of a Numbers or Tally table is to get rid of loops.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45382 Visits: 39938
yuvalpe (11/24/2008)
This topic was cover complitly by Itzik Ben-Gan years ago


So? There's lot's of folks that don't even know who he is, never mind what a Numbers or Tally table is.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
peter-757102
peter-757102
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 2549
jacroberts (11/24/2008)
I'd consider using a table valued function instead that only returns the rows I'm interested in:

e.g.:
CREATE FUNCTION [dbo].[Nums]
(
@StartNum int,
@EndNum int
)
RETURNS @table TABLE (Num int)
AS BEGIN

DECLARE @tmpNum int
SET @tmpNum = @StartNum
WHILE @tmpNum < @EndNum
BEGIN
INSERT INTO @table VALUES (@tmpNum)
SET @tmpNum = @tmpNum + 1
END

RETURN
END
GO

Example Usage:
SELECT * FROM Nums(1000, 2000)


Unfortunatly non-inline functions, including table valued ones, run miserably slow in SQL Server. I hope they change it in the future, but for now solutions like yours (and I used similar in the past) just won't deliver performance anywhere near that of a numbers table.

The main issue in your solution (and solutions like it) is that multiple statements are exectuted. Each insert performed within the loop carries so much overhead that just reading a table from a cached page performs tons faster. The set based aproach only has to deal one time with the overhead compared to one time per iteration for the procedural one. On top of it...the return table (called @table in your case) is stored in the temp DB, so data is written to disk (which is slow), compared to a number table, which is just reading data, likely to already be in the cache.

So as things stand:

* When using a function, always try to use a inline table valued function as this is integrated in your query.
* Do not fill up a table one row at a time (Jeff Moden calls it RBAR for good reason)

That said, there are occasions where a non-inline table valued function does better then a inlined one (I recently has such a case). In certain complex queries where the optimiser makes wrong choices this can happen. But you should still fill up the result table in as few statements as possible, which is where a good number table or non-procedural number function comes in.

There are good examples of number/tally functions to be found of this forum. Currently I am still in the process of optimizing one I found and modified. Testing unfotunatly takes time and I want the solution to be scalable across at least several threads, once its finished and if I do succeed, it will end up on this site as well for everyone to use.
Jonathan AC Roberts
Jonathan AC Roberts
SSC-Addicted
SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)SSC-Addicted (430 reputation)

Group: General Forum Members
Points: 430 Visits: 1836
It depends how much performance is an issue, for a lot of queries it is not. For example, if there is a daily report that runs in batch it would not matter. I've tested the query on my development environment it generated and queried the table with 4000 rows in in less than 1 second.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search