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

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. Expand / Collapse
Author
Message
Posted Sunday, April 7, 2013 2:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
Jeff Moden (4/7/2013)

With the understanding that I've not seen nor have I been able to produce such a failure except when an indeterminate formula was used as the target of the ORDER BY, I guess the next question would be, is there a code example somewhere that demonstrates failure of the TOP (MaxIntValue)/ORDER BY? The one that MS published never seems to fail.

As long as you are not querying tables with more than maxint rows, this method currently always works. Just as the version with TOP 100 PERCENT always worked until it stopped working in SQL Server 2005. But as far as I know, it's not documented, nor guaranteed.

Also, is there a work around that will produce the desired behavior other than the obvious method of shifting to FOR XML PATH? Or, is that also subject to ORDER BY failing (which would really screw up some real XML)?

There are two issues here, and we should take care not to confuse them.

1: ORDER BY in a subquery (or view) - except for some specific exceptions, this is not allowed. In the situations where it is allowed (off the top of my head only TOP and FOR XML), the ORDER BY is guaranteed to be used to specify what rows to qualify/disqualify for the TOP and is also guaranteed to process the rows in the specified order when forming the XML. But not guaranteed to do anything else. If you have TOP 5 ... ORDER BY FirstName in a subquery or view, you will only get names like Aaron and Abby, but unless there is an ORDER BY in the outer query as well, you can still get them in any order. They usually stay in alphabetical order, but this is not guaranteed. For the FOR XML construct, the result is just a single row, so ordering does not apply at all anymore.

2: Concatenation by variable assignment (SELECT @x = @x + Column FROM ...), either with or without ORDER BY. I have once seen a website with a number of examples that were reproducible and that showed this technique to be unreliable. Unfortunately, I lost the URL and have never been able to find it again. And frankly, I have no idea if any of those examples would have reproduced on newer versions (like SQL Server 2005, or up - yes, it is THAT long ago since I found that site).
There is a very weird article in the Knowledge Base: http://support.microsoft.com/kb/287515. It first says that "the correct behaviour for an aggregate concatenation query is undefined" (which as far as I know is still true). However, it then further down does provide a workaround "to achieve the expected results from an aggregate concatenation query", without bothering to specify what the "expected results" for this undefined behaviour should be.

Also, is there a work around that will produce the desired behavior other than the obvious method of shifting to FOR XML PATH? Or, is that also subject to ORDER BY failing (which would really screw up some real XML)?

The only guaranteed methods I know to correctly concatenate strings are:
1: FOR XML
2: Procedural code (i.e. looping over the result set)
3: Creating your own CLR user-defined aggregate function. When this feature was new, there was no option to preserve order in CLR user-defined aggregates (there was syntax for it, but it had no effect). I have no idea if this has changed since; I think it has not in SQL Server 2008, but I stopped paying attention after that.

If you use ORDER BY and FOR XML at the same level of the query, you can rely on the results being added to the XML result in the desired order. If you use ORDER BY at a different subquery-level, it's anyone's guess.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1439667
Posted Thursday, June 13, 2013 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 3, 2014 7:03 AM
Points: 1, Visits: 107
I have a quick question, in the original post the final output is row by row. How do you get it into columns using id? Thanks.
Disregard. I figured it out I needed to key off the tabby value! What an amazingly quick solution! Thanks again.
Post #1463202
Posted Thursday, June 13, 2013 10:21 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 36,787, Visits: 31,245
rayoftennessee (6/13/2013)
I have a quick question, in the original post the final output is row by row. How do you get it into columns using id? Thanks.
Disregard. I figured it out I needed to key off the tabby value! What an amazingly quick solution! Thanks again.


Hi Ray,

Thanks for stopping by and the thoughtful comment.

Do you have a coded solution that you'd like to share for the problem you mentioned above? It might help someone else out with a similar problem.


--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 #1463378
Posted Tuesday, September 17, 2013 3:39 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, October 31, 2013 11:22 AM
Points: 160, Visits: 231
*uuh*

Hi, Jeff,

my thanks to you for your great articles here on SSC as well as your comments outside.

And it was good of you to answer the idea regarding the use of recursion in detail: I learned a lot today!

Best regards

JP


________________________________________________________
If you set out to do something, something else must be done first.
Post #1495417
Posted Thursday, January 2, 2014 5:37 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 388, Visits: 1,532
Hi Jeff,

I apologize if you've addressed this elsewhere, but I was sort of curious if you'd done any testing of tally/inline tally tables vs. sequences in 2012?

http://technet.microsoft.com/en-us/library/ff878091.aspx

I'd be interested to see them thrown into the mix.

Thanks,
Erik
Post #1527325
Posted Thursday, January 2, 2014 5:43 PM


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 @ 12:42 PM
Points: 3,374, Visits: 7,299
I'm not sure that sequences work the same way as a Tally table. It seems that their purposes are different.
Could you explain how would you use a sequence instead of a tally table?



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1527327
Posted Thursday, January 2, 2014 6:18 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 388, Visits: 1,532
Hi Luis,

I recently saw a PostgreSQL example where a series of dates was generated that reminded me a bit of tally table code. That's what got me curious about sequences in MS SQL. I'm not entirely sure how I'd use it here, but wanted to ask in case one of you vastly smarter people had already thought of something

http://pgexercises.com/questions/aggregates/rollingavg.html

select 	dategen.date,
(
-- correlated subquery that, for each day fed into it,
-- finds the average revenue for the last 15 days
select sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) as rev

from cd.bookings bks
inner join cd.facilities facs
on bks.facid = facs.facid
where bks.starttime > dategen.date - interval '14 days'
and bks.starttime < dategen.date + interval '1 day'
)/15 as revenue
from
(
-- generates a list of days in august
select cast(generate_series(timestamp '2012-08-01',
'2012-08-31','1 day') as date) as date
) as dategen
order by dategen.date;

Post #1527331
Posted Friday, January 3, 2014 8:18 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 36,787, Visits: 31,245
Thanks for stopping by, Erik.

From what I understand, the SEQUENCEs of 2012 aren't the same as the "Generate_Series" functionality of PostGre. SEQUENCEs still rely on rows being present where "Generate_Series" generates a row set. Not quite the same thing.


--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 #1527562
Posted Friday, January 3, 2014 8:36 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 @ 12:42 PM
Points: 3,374, Visits: 7,299
Generate_Series sounds like a great function, but it's not the same as Sequences in SQL Server (or Oracle FWIW).
Sequences are used to fetch values but they will keep track of the last value used to return the following value the next time the sequence is used. Sequences are similar to identity column values but are used different and are independent objects.

For something similar to Generate_Series, some people have constructed iTVFunctions for tally or calendar tables built on the fly.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1527577
Posted Friday, January 3, 2014 9:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 388, Visits: 1,532
Yeah, I saw the limitations last night after I had a chance to mess with it. I was hoping there would be a quick way to use the sequence values as positions in data or something. Ah well.

Thanks for the replies
Post #1527596
« Prev Topic | Next Topic »

Add to briefcase «««3839404142»»»

Permissions Expand / Collapse