Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

The Numbers Table Expand / Collapse
Author
Message
Posted Sunday, November 23, 2008 10:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 3, 2011 8:48 AM
Points: 26, Visits: 71
Comments posted to this topic are about the item The Numbers Table
Post #607292
Posted Monday, November 24, 2008 1:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:21 AM
Points: 256, Visits: 440
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

Post #607349
Posted Monday, November 24, 2008 2:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 36,975, Visits: 31,492
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #607370
Posted Monday, November 24, 2008 2:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 27, 2009 2:01 AM
Points: 1, Visits: 15
This topic was cover complitly by Itzik Ben-Gan years ago
Post #607377
Posted Monday, November 24, 2008 3:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 5,342, Visits: 1,387
Earlier coverage was more than this. But this one also is good. :)


Post #607398
Posted Monday, November 24, 2008 5:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:56 AM
Points: 338, Visits: 1,422
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)
Post #607439
Posted Monday, November 24, 2008 6:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 36,975, Visits: 31,492
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #607461
Posted Monday, November 24, 2008 6:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 36,975, Visits: 31,492
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #607464
Posted Monday, November 24, 2008 6:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 7, 2014 4:27 AM
Points: 324, Visits: 2,212
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.
Post #607466
Posted Monday, November 24, 2008 6:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:56 AM
Points: 338, Visits: 1,422
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.
Post #607485
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse