February 13, 2013 at 8:34 am
Great question. I have needed to use this technique when producing door tags for temporary water shutdowns. Each Service that we have has a Unit Count. Most services are for single family homes, but we have others for duplexes and condo's and apartments.
So if we are shutting down a water main and it affects a duplex I need two door tags for that service. If an apartment complex is going to be out of service I need one for each apartment.
So to get the doortag records, I join the affected services to a tally table on tallytable.integer <= AffectedService.unitCount Basically the unitCount is used to produce the appropriate number of coppies for each service.
February 13, 2013 at 5:03 pm
We have used cross joins for things like this as well. We used to do sign-off sheets at the end of payroll cycles so managers could have their employees sign off on the amount of hours they worked for the pay period. We ended up converting that report along with most of the rest to SSRS. Then we could just run a straight pull and handle the blank lines through the table layout. We still occasionally use cross joins for other reporting purposes but not very often.
Nice question and thorough explanation.
February 18, 2013 at 11:37 pm
Very good question. It occupied me for litlte more time than usual. 🙂
February 26, 2013 at 6:20 am
Archie is correct - the question and its explanation is incorrect. The QotD feature should teach, and teaching to neglect relational principles and rely on implementation details is questionable. Yes, MS SQL (any version I've ever known back to 6.5 if I remember correctly) will output rows in clustered index order when unspecified, but no, this shouldn't factor in this context. Query 3 cannot guarantee the result will be as asked.
February 26, 2013 at 8:14 am
sqlcentral-bugmenot (2/26/2013)
Archie is correct - the question and its explanation is incorrect. The QotD feature should teach, and teaching to neglect relational principles and rely on implementation details is questionable. Yes, MS SQL (any version I've ever known back to 6.5 if I remember correctly) will output rows in clustered index order when unspecified, but no, this shouldn't factor in this context. Query 3 cannot guarantee the result will be as asked.
You are not correct. SQL Server will usually output rows in clustered index order when unspecified - but at least since SQL 7.0, maybe even before, other orders have always been possible. Check http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx for an example.
Other (less amusing, but probably more common) reasons for getting a different order include:
* Parallellism. Run a big query against a large table on a system with multiple processors, and you'll often see jumps in the range. Order is not preserved when gathering the parallel streams, unless requested.
* "Piggyback scan". Quite rare, and Enterprise Edition only - this feature allows a scan of a table or index to get rows from another scan that is already processing, then restart at the start until it reaches the point where it started piggybacking. If no parallellism is involved, this feature would show in the output as results starting somewhere halfway in the table, continuing in order of the index being scanned until the end, and then starting fro the beginning until the starting point of the results. (So when an index on an integer is used, you'd get results from 12,659, 12,660, ... until the maximum, then 1, 2, ..., 12,658)
* IAM scan. Also rare, because it requires either read uncommitted isolation level (which should be much less common that it actually is) or a full table lock (which is exactly asa rare *** it should be). If a table is large enough and the locking requirement is met, an IAM scan will scan pages in physical allocation order instead of their logical order.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply