Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sitka
Aged Yak Warrior
571 Posts |
 Posted - 2004-01-20 : 10:03:40
|
| Refering to ..WITH ROLLUP. Is it neccessary to force the order on the result set with an ORDER BY or will the returned set alwaysmaintain that natural grouping column default which is nice.It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-20 : 10:43:56
|
| Have you tried it?I would have thought that with an order by the rollup rows would be sorted to be before the groups they are totalling due to the null entries unless you put in a case statement.I suspect that the rollup rows will always come after the groups they are totalling but don't think you can rely on the groups being in any order==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-01-20 : 11:57:28
|
| Thanks nr, I did take some stabs at it.The default way; it comes out is perfect.Kind of got fooled I think, I added an Order BY and after first run it was a lot slower,then I posted here. Should have ran a couplemore times first 'cause it dosen't impact much. The explicit ORDER BY adds an additional SORT;comes to 3% of total batch in the query plan.Guess that is what really matters, both Queries are under two secondsin this case which is good and very close to the same.I don't understand execution plans, but moving right to leftonce all the subtrees become a single branch the first operation is a sort.This isn't specifically called within the query but it is on the equivalentcolumns (on the bottom of K.H.'s page 153) as in the example st.store_name and t.type.So in effect I'm doubling that up. I guess I'll have to check some other ROLLUPQueries to see if that SORT is always resident as an early node on the finalbranch.(the more I look at it the more it seems the Rollup rows are dependent on order so if the order wasn't there the rollup wouldn't work. That might be what you are trying to tell me?)It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-20 : 20:58:03
|
| ROLLUP and CUBE are not dependent on order, they summarize groups based on values only. When ordering a result set with ROLLUP or CUBE, the GROUPING function makes it easy to locate the summary rows where you want them. Look up GROUPING in Books Online under ROLLUP and CUBE for examples. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-01-21 : 09:11:31
|
| Thanks,I tried various Grouping() functions and it is awesome.That report came out sweet and fast. Right now I'm liking Rollup.I was just wondering if Rollup infact gaurentees Order accross allgroups.If there is a Rollup two deep like in Ken H. guide is the orderassured for the whole set AND groups. Right now I'm interpretingyes, because if you look at a query plan and there is aStream Aggregate operator near the output, and if it isbased on what would be your desired final order by clauseanyways; It's like the work is already done.Any Comment on this Stream Aggregate physical operator,Experience will hopefully let me know if it is at play inall With RollUp options. BOL states Stream Aggregate asbeing order dependent within each group. So maybe Rollup ongroups assures Order there and then Rollup on the whole setassures Order there.Can't wait for the next time I need Rollup, it's fun. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-21 : 23:14:53
|
| ROLLUP and CUBE are the bee's knees, probably the best additions ever made to T-SQL.The Stream Aggregate is a general operator that is used whenever you have an aggregate function like Count() or Sum(), you'd see it in the plan even without ROLLUP. GROUP BY used to automatically ORDER BY in earlier versions of SQL Server (6.5 and before) but since v7.0 the optimizer was changed and it no longer guarantees an ordered set (I sometimes think it deliberately puts them OUT of order) It's possible that you'll get a specific order for free anyway, but as always if you need a guaranteed ordering you must use ORDER BY. |
|
|
|
|
|
|
|
|
K.H. Guru Guide; ROLLUP Page 153-154