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

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 2:33 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:05 AM
Points: 118, Visits: 226
jjturner (4/5/2013)
hmm... seeing that my data is in Access and the recordsets are pretty light, I may opt to keep everything local with the VBA cursor on a custom function.
Otherwise I'd have to somehow pass the recordset from Access into the whole sp call. But thanks again for laying the groundwork on the ADO parameter piece!

Cheers,
John


Nope. 'Pass Through Query' is only the term. From a developer standpoint, a Pass Through Query is just another recordset.


We should open a new thread as this is off topic.
Post #1439484
Posted Saturday, April 6, 2013 6:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
david.holley (4/5/2013)
I don't know if you can do that with a TallyTable or if a TT is neccessary. This is a function that should accomplish it. It simply adds the next selected value to the previous. And no, I didn't figure this out myself.

You need to add something to that code to check that you are getting things in the right order and do something else if not. The order of elements in the result string is not guaranteed to be determined by the order by clause, so you need to include code for each row that checks the required order has not yet been violated and force an error if it has. That is actually veru easy to do, only a small modification. But you also have to have code to deal with the error, presumably by using an explicity loop to do the job instead of the single statement.

Whether order the elements of the result string are in depends on how the optimiser decides to execute that query, which potentially changes when the table content changes or when the optimiser is updated (hotfix, service pack, or new release) or when an index on the table is added, altered, or dropped.

If the table is clustered on the id column and there is no covering index for the query, or the table is clustered on a covering index for the table whose first element is id, of the table is an heap with a covering index for the query and that indexes first element ois id, you stand a good chance of getting the behaviour you want (in most other cases you don't) but it's still not guaranteed. Also, if the query works today that doesn't guarantee it will work tomorrow - for example adding some rows to the table (or deleting some) may cause the optimiser to adopt a different query plan. And it may not work after applying an update to SQL Server or in a new release, because the optimiser may have changed a bit and adopt a different query plan.


Tom
Post #1439580
Posted Saturday, April 6, 2013 5:17 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:22 AM
Points: 35,772, Visits: 32,443
L' Eomot Inversé (4/6/2013)
The order of elements in the result string is not guaranteed to be determined by the order by clause, so...


I've heard about that but haven't been able to cause it. Do you have a link or some code that demonstrates the 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 #1439609
Posted Saturday, April 6, 2013 5:20 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:22 AM
Points: 35,772, Visits: 32,443
jjturner (4/5/2013)
Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.

Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...

Cheers,
John


Although the function that David demonstrated is a tried and true method, there's no need for a function at all. It can be done using an XML hack that is quite popular. Please see the following article.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/


--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 #1439610
Posted Saturday, April 6, 2013 8:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
Jeff Moden (4/6/2013)
L' Eomot Inversé (4/6/2013)
The order of elements in the result string is not guaranteed to be determined by the order by clause, so...


I've heard about that but haven't been able to cause it. Do you have a link or some code that demonstrates the problem?

No, unfortunately I don't have anything that demonstrates it. I think I have experienced it, though.

I was bitten by it, I think, back in 2005 - it started happening with our system on a machine belonging to one of our customers, but not on other customers' machines and not in-house, although the code and the relevant part of the schema was identical in all the machines, both those affected and those not - only the data was different according to (a) what the customer had contracted for and (b) what use the customer had made of his stuff, and the hardware was different according to when it had been installed. We ended up changing that piece of code on all the systems to do it with a loop, since our customers expected 24 X 7 correct operation apart from a monthly break (scheduled ad hoc by the customer to fit his needs, not the same time every month) to apply MS patches and any of our fixes that would need downtime. Unfortunately this was at a time when I was swamped with too much administrative work (we didn't have enough people) so although the exact details were on file in our support record I didn't make a personal copy. Now I no longer have access - I left the company nearly 4 years ago, after transferring responsability for all technical matters from London to Beirut, and since then the company's London and Dubai operations have been reduced even further so the record may no longer exist.

edit: I should probably mention that in the cases where I said there's a good chance, my personal belief is that it will always work in those cases - but people whom I probably ought to believe have told me there's no guarantee even in those cases. And where I've said there's not such a good chance, I suspect that the chance of it working is still quite high, but think I have seen it fail once.


Tom
Post #1439614
Posted Sunday, April 7, 2013 6:52 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:05 AM
Points: 118, Visits: 226
Would wrapping the SELECT inside another work if the specific order is a concern? Something like...

SELECT @NewString = @NewString + NameInitials FROM
(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s
Post #1439639
Posted Sunday, April 7, 2013 7:39 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:22 AM
Points: 35,772, Visits: 32,443
L' Eomot Inversé (4/6/2013)
Jeff Moden (4/6/2013)
L' Eomot Inversé (4/6/2013)
The order of elements in the result string is not guaranteed to be determined by the order by clause, so...


I've heard about that but haven't been able to cause it. Do you have a link or some code that demonstrates the problem?

No, unfortunately I don't have anything that demonstrates it. I think I have experienced it, though.

I was bitten by it, I think, back in 2005 - it started happening with our system on a machine belonging to one of our customers, but not on other customers' machines and not in-house, although the code and the relevant part of the schema was identical in all the machines, both those affected and those not - only the data was different according to (a) what the customer had contracted for and (b) what use the customer had made of his stuff, and the hardware was different according to when it had been installed. We ended up changing that piece of code on all the systems to do it with a loop, since our customers expected 24 X 7 correct operation apart from a monthly break (scheduled ad hoc by the customer to fit his needs, not the same time every month) to apply MS patches and any of our fixes that would need downtime. Unfortunately this was at a time when I was swamped with too much administrative work (we didn't have enough people) so although the exact details were on file in our support record I didn't make a personal copy. Now I no longer have access - I left the company nearly 4 years ago, after transferring responsability for all technical matters from London to Beirut, and since then the company's London and Dubai operations have been reduced even further so the record may no longer exist.

edit: I should probably mention that in the cases where I said there's a good chance, my personal belief is that it will always work in those cases - but people whom I probably ought to believe have told me there's no guarantee even in those cases. And where I've said there's not such a good chance, I suspect that the chance of it working is still quite high, but think I have seen it fail once.


Thanks, Tom. I've never actually seen the out-of-order problem happen either and it might be because of the precautions I normal take when doing such things. I do suspect (but haven't taken the time to prove) that the problem occurs when the unique and or clustered indexes are just right and parallelism occurs. Because of the possibility of it coming out wrong, I normally add the ol' OPTION(MAXDOP 1) to such a thing when I can. I can imagine that a join within the concatenation overlay in a single variable might still gum up the works. That might also be why I've never seen it. I try to keep my code pretty simple and modular. I don't mix joins with such string tricks except for the Tally Table.

I do wish that MS had two different ORDER BYs... one that occurred as the processing occurred and one after the processing completed.


--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 #1439642
Posted Sunday, April 7, 2013 7:48 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:22 AM
Points: 35,772, Visits: 32,443
david.holley (4/7/2013)
Would wrapping the SELECT inside another work if the specific order is a concern? Something like...

SELECT @NewString = @NewString + NameInitials FROM
(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s


Damn. I was just looking at an MS white paper last night (for a totally different reason) that said this is no guarantee, either, and didn't save the URL. But I believe I know how to fix even that problem.

First, since you're using ORDER BY in a sub-query (Derived Table, in this case), you must also use TOP. That's where most people end up making the mistake. For simplicity, most will use TOP 100%. I can't prove it but I remember one of the folks over on the SQLTeam forums proving that TOP 100% can sometimes cause the ORDER BY to be ignored in a sub-query. Instead, they recommended the use of the max integer value of 2147483647.

As a sidebar, I kick myself everytime I remember something like this and forget to save the URL from where I found it some years ago.

Of course, the "best" way to do it now is to use the XML hack that I provided a link for.


--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 #1439643
Posted Sunday, April 7, 2013 10:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:36 PM
Points: 6,133, Visits: 8,398
Jeff Moden (4/7/2013)
david.holley (4/7/2013)
Would wrapping the SELECT inside another work if the specific order is a concern? Something like...

SELECT @NewString = @NewString + NameInitials FROM
(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s


Damn. I was just looking at an MS white paper last night (for a totally different reason) that said this is no guarantee, either, and didn't save the URL. But I believe I know how to fix even that problem.

First, since you're using ORDER BY in a sub-query (Derived Table, in this case), you must also use TOP. That's where most people end up making the mistake. For simplicity, most will use TOP 100%. I can't prove it but I remember one of the folks over on the SQLTeam forums proving that TOP 100% can sometimes cause the ORDER BY to be ignored in a sub-query. Instead, they recommended the use of the max integer value of 2147483647.

As a sidebar, I kick myself everytime I remember something like this and forget to save the URL from where I found it some years ago.

Of course, the "best" way to do it now is to use the XML hack that I provided a link for.


Until SQL Server 2000, the plan would include a sort, in order to satisfy the TOP clause. Usually, that sort order would then be retained throughout the rest of the plan - though that was never guaranteed - if the order by is in a subquery, it does not define the order in which rows of the final query are returned.
Then, in SQL Server 2005, the optimizer was improved to recognize a "TOP 100 PERCENT" as superfluous. And that meant that the sort step, which was only needed to execute that TOP clause, could be removed from the plan as well. This broke an enormous amount of code that relied on this trick, in spite of it being undocumented and many experts warning against it. (Some of that code was in use by Microsoft customers with enough influence that MS even released a hotfix that implemented a trace flag you could activate return to the old behaviour, and that trace flag survived into SQL Server 2008 as well - see http://support.microsoft.com/kb/926292).

I've seen the advise to use TOP with an insane high integer value, but I don't support it. First, there are tables that include more rows, and then this TOP clause could cause incorrect results to be returned. But probably more important: peoplpe should consider that a future improvement of the optimizer might also include an optimization to remove the top and associated sort if the optimizer can be sure that the actual number of rows will never come evenn near that amount. At that point, all code using this trick can stop working.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1439651
Posted Sunday, April 7, 2013 12:58 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:22 AM
Points: 35,772, Visits: 32,443
Hugo Kornelis (4/7/2013)
Jeff Moden (4/7/2013)
david.holley (4/7/2013)
Would wrapping the SELECT inside another work if the specific order is a concern? Something like...

SELECT @NewString = @NewString + NameInitials FROM
(SELECT NameInitials FROM Employees WHERE OfficeId = 1001 ORDER BY NameInitials) as s


Damn. I was just looking at an MS white paper last night (for a totally different reason) that said this is no guarantee, either, and didn't save the URL. But I believe I know how to fix even that problem.

First, since you're using ORDER BY in a sub-query (Derived Table, in this case), you must also use TOP. That's where most people end up making the mistake. For simplicity, most will use TOP 100%. I can't prove it but I remember one of the folks over on the SQLTeam forums proving that TOP 100% can sometimes cause the ORDER BY to be ignored in a sub-query. Instead, they recommended the use of the max integer value of 2147483647.

As a sidebar, I kick myself everytime I remember something like this and forget to save the URL from where I found it some years ago.

Of course, the "best" way to do it now is to use the XML hack that I provided a link for.


Until SQL Server 2000, the plan would include a sort, in order to satisfy the TOP clause. Usually, that sort order would then be retained throughout the rest of the plan - though that was never guaranteed - if the order by is in a subquery, it does not define the order in which rows of the final query are returned.
Then, in SQL Server 2005, the optimizer was improved to recognize a "TOP 100 PERCENT" as superfluous. And that meant that the sort step, which was only needed to execute that TOP clause, could be removed from the plan as well. This broke an enormous amount of code that relied on this trick, in spite of it being undocumented and many experts warning against it. (Some of that code was in use by Microsoft customers with enough influence that MS even released a hotfix that implemented a trace flag you could activate return to the old behaviour, and that trace flag survived into SQL Server 2008 as well - see http://support.microsoft.com/kb/926292).

I've seen the advise to use TOP with an insane high integer value, but I don't support it. First, there are tables that include more rows, and then this TOP clause could cause incorrect results to be returned. But probably more important: peoplpe should consider that a future improvement of the optimizer might also include an optimization to remove the top and associated sort if the optimizer can be sure that the actual number of rows will never come evenn near that amount. At that point, all code using this trick can stop working.


Thanks for the info and the link, as well.

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.

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)?


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

Add to briefcase «««3738394041»»»

Permissions Expand / Collapse