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 «««3940414243»»»

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. Expand / Collapse
Author
Message
Posted Friday, January 3, 2014 9:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 13, 2014 11:51 AM
Points: 115, Visits: 223
Disney uses a AS/400 based PMS which uses sequences on every record. I never started a conversation as to it purpose, but I'm assuming that it provides a means of (almost) guaranteeing that each transaction record (regardless of table) is (almost) uniquely identified. The sequence reset at 10,000,000,000. Depending on the retention level, the same sequence number could appear more than once at the same time in the db regardless of table. The probability of the same sequence number occurring more than once in the same table being miniscule. If memory serves the primary key was composed of the transaction date plus the sequence number. I can't remember if the sequence was stored separately or combined with the trans date. It's only been 13 years.
Post #1527626
Posted Friday, July 4, 2014 5:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:56 AM
Points: 1, Visits: 6
Thank you for this article.
I'm very new in SQL-Server field and it was very useful for me, but I have one question which not clear enough for me:

Suppose I want to use "Tally table" in my procedure, and my co-worker planning to use "Tally table" in his procedure.
Do we have to use different Tally table for each procedure.
If it so, I think it will be so many different "Tally tables" in our database (one Tally table on each procedure).
If we could use the only Tally table for each procedure then how to fill this table sequential data? I need 1000 rows, somebody else can decide that it's too much and decrease the amount...

Please clear this moment for me...
Post #1589314
Posted Friday, July 4, 2014 8:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 3,559, Visits: 7,681
You don't need a different tally table for each procedure. You only need one and you'll just determine the range when you query it.
You could as well create a view instead of a physical tally table
CREATE VIEW vTally 
WITH SCHEMABINDING
AS
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) n
FROM E4

or a table valued function
CREATE FUNCTION fTally(@From int, @To int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
SELECT TOP( @To - @From + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + @From - 1 n
FROM E4

Which run with 0 reads as they're calculated on the fly.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1589364
Posted Friday, July 4, 2014 9:35 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
Personally, I'd much prefer the iTVF (inline Table Valued Function) over the view because it runs as fast as the view and is much easier to control the range of numbers returned.

Shifting gears a bit, I don't try to calculate ranges that start with other than 0 or 1 in my Tally function because it adds a bit (small but still present) of unnecessary overhead. Most of the work I do starts at 0 or 1 and when dealing with large ranges and large amount of data, every bit helps even at the speed of today's machines. If I need a range to be offset, I do that calculation externally. I also do non-whole number and non-sequential sequences to having just a base 0 or 1 to whatever function works the best for me.


--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 #1589399
Posted Friday, July 4, 2014 1:07 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
my2ndbox (7/4/2014)
Thank you for this article.
I'm very new in SQL-Server field and it was very useful for me, but I have one question which not clear enough for me:

Suppose I want to use "Tally table" in my procedure, and my co-worker planning to use "Tally table" in his procedure.
Do we have to use different Tally table for each procedure.
If it so, I think it will be so many different "Tally tables" in our database (one Tally table on each procedure).
If we could use the only Tally table for each procedure then how to fill this table sequential data? I need 1000 rows, somebody else can decide that it's too much and decrease the amount...

Please clear this moment for me...


You only need one Tally Table or one Tally function such as that which Luis posted. I use both.

My Tally Table is setup for 11,000 rows because I do a fair bit of work with mortgages. 11,000 rows is a number just a little larger than 30 years worth of days. The article explains how to build one. Make sure that you don't forget the unique clustered index in the form of a PK.

The Tally function that I use looks like this. It will allow a starting value of either 0 or 1 with virtually no overhead for making that decision.

/****** Object:  UserDefinedFunction [dbo].[fnTally]    Script Date: 07/04/2014 15:02:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Billion.

As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne, @MaxN) t
;

Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URL for how it works. http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool.
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);

8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10E1 or 10 rows
, E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or 1 Thousand rows
, E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or 1 Billion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E9 -- Values from 1 to @MaxN
;




--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 #1589443
Posted Monday, July 7, 2014 6:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 13, 2014 11:51 AM
Points: 115, Visits: 223
If you're working with a specific range of numbers, for example 1980 to 2020, simply add that to the WHERE statement.



SELECT [Value]
FROM [sysNumbers]
WHERE Value BETWEEN 13 and 17



Post #1589817
Posted Monday, July 7, 2014 8:27 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
david.holley (7/7/2014)
If you're working with a specific range of numbers, for example 1980 to 2020, simply add that to the WHERE statement.



SELECT [Value]
FROM [sysNumbers]
WHERE Value BETWEEN 13 and 17





I have to ask, how do the values of 13 thru 17 translate to "1980 to 2020" in that example?


--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 #1589900
Posted Monday, July 7, 2014 8:46 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:47 AM
Points: 371, Visits: 964
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!

Cheers
Post #1589920
Posted Monday, July 7, 2014 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 13, 2014 11:51 AM
Points: 115, Visits: 223
Jeff Moden (7/7/2014)
david.holley (7/7/2014)
If you're working with a specific range of numbers, for example 1980 to 2020, simply add that to the WHERE statement.



SELECT [Value]
FROM [sysNumbers]
WHERE Value BETWEEN 13 and 17





I have to ask, how do the values of 13 thru 17 translate to "1980 to 2020" in that example?


They don't. I did a simple select on the table that I have and then realized that I don't go up that hight.
Post #1589924
Posted Monday, July 7, 2014 9:05 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
jfogel (7/7/2014)
Great, just great. A year ago I wrote something that has to break down a CSV-style parameter string and it uses a loop to do it. It is just a small part of a larger process and it runs quickly; however, this has me thinking about some other things I've done with loops that work and are "good enough", but now I see some serious potential to speed some of them up. So, thank you, Jeff for causing me to feel embarrassed and have to go clean up a mess I made!


Heh... it's a funny thing. I had kind of a mentor for a week when I first started out. I asked him a question about how to set a variable for each row and his reply was something to the effect of "For that, you'd need a cursor... and if you ever write one, I'm all done teaching you". Of course, he was also implying the use of WHILE loops.

As a result, I didn't write a WHILE loop for anything for production use for years. Didn't even know how to write one.

Anyway, if you want a good CSV splitter with the understanding that it was designed to only split single character delimiters and was NOT designed to split the MAX datatypes (runs twice as slow if you try), please see the following article. Lot's of people had some really great input to the function over the years.
http://www.sqlservercentral.com/articles/Tally+Table/72993/


--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 #1589945
« Prev Topic | Next Topic »

Add to briefcase «««3940414243»»»

Permissions Expand / Collapse