Does RS use ORDER BY for grouping?

It seems like you don't need to specify SQL's ORDER BY to match Page and Group breaks in RS. I.e., RS does
this handling by itself, with the added work and memory requirements to do it.
My question is whether RS will understand that I do have ORDER BY in the query that matches my Page and Group
breaks, and just read off the input stream without doing the sorting etc by itself. If it doesn't, I wouldn't
want to burden the DBMS with the sorting as it would be redundant...
I tried a report, which was indeed alphabetically grouped by RS without a SQL ORDER BY. Even when adding ORDER
BY NEWID() (which returns rows in a different "random ordering for each execution), the report was still
grouped alphabetically.
(I am not referring to the ordering of the details info, I understand that ORDER BY is needed for that.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/You don't ever need to use ORDER BY in your query, even to order the
details. RS supports many different types of data sources (and you can
easily add your own by writing a custom data processing extension), not just
SQL, and some these data sources may not have the ability to sort. Also, RS
does not examine your query and cannot detect that you have an ORDER BY.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OPN1N6ifEHA.1644@.tk2msftngp13.phx.gbl...
> It seems like you don't need to specify SQL's ORDER BY to match Page and
> Group breaks in RS. I.e., RS does
> this handling by itself, with the added work and memory requirements to do
> it.
> My question is whether RS will understand that I do have ORDER BY in the
> query that matches my Page and Group
> breaks, and just read off the input stream without doing the sorting etc
> by itself. If it doesn't, I wouldn't
> want to burden the DBMS with the sorting as it would be redundant...
> I tried a report, which was indeed alphabetically grouped by RS without a
> SQL ORDER BY. Even when adding ORDER
> BY NEWID() (which returns rows in a different "random ordering for each
> execution), the report was still
> grouped alphabetically.
> (I am not referring to the ordering of the details info, I understand that
> ORDER BY is needed for that.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>

does grouping on a field imply sorting by that field?

If I group on a field, will the report be sorted by that field as well?
I have a table whose parent is the report body. I'm grouping the
table on a string field that contains values like "SC2", "SC4", "SC5",
and "SC6". These values are all 3 characters long (i.e., no white
space) and all uppercase. When I preview the report, the groups appear
in this order: "SC2", "SC6", "SC5", and "SC4" - which is the order
these values appear in the grid on the Data tab - so it appears that no
sort is being applied to the dataset (which, BTW, is based on a stored
procedure). I have tried sorting by this field (in addition to
grouping by it) but that does not change the order displayed in the
report. Any help would be appreciated.Where are you applying the sort? You need to sort the groups, not the
details (or dataset). In the Group Properties dialog, select he sort tab and
you should get what you want.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <leemsrs@.hotmail.com> wrote in message
news:1114273595.800769.209160@.l41g2000cwc.googlegroups.com...
> If I group on a field, will the report be sorted by that field as well?
> I have a table whose parent is the report body. I'm grouping the
> table on a string field that contains values like "SC2", "SC4", "SC5",
> and "SC6". These values are all 3 characters long (i.e., no white
> space) and all uppercase. When I preview the report, the groups appear
> in this order: "SC2", "SC6", "SC5", and "SC4" - which is the order
> these values appear in the grid on the Data tab - so it appears that no
> sort is being applied to the dataset (which, BTW, is based on a stored
> procedure). I have tried sorting by this field (in addition to
> grouping by it) but that does not change the order displayed in the
> report. Any help would be appreciated.
>|||Knowing where to apply the sort is the confusing part for me. There is
a Sorting tab on the Table Properties dialog (my report consists of a
single table whose parent is the report body). There is also a Sorting
tab on the Grouping and Sorting Properties dialog (which can be
accessed for each group). What are these two different Sorting tabs
used for? Tell me if I got it right in the following example: the
report my users want should be grouped by Field A, and then within each
Field A group it should be grouped by Field B. Finally, within each
Field B group it should be sorted (not grouped) by Field C. On the
Grouping and Sorting Properties dialog for the Field A group, I went to
the Sorting tab and chose Fields!FieldA.value. Next, on the Grouping
and Sorting Properties dialog for the Field B group, I went to the
Sorting tab and chose Fields!FieldB.value. Finally, on the Table
Properties dialog, I went to the Sorting tab and chose
Fields!FieldC.value as the only sort field. This seems to produce the
grouping and sorting that my users want, but because I find the process
counter-intuitive (my 4 years of previous reporting experience have
been with Cognos Impromptu), I'm wondering if what I did is correct.
Can you tell me?|||You are correct. The sorting on the grouping dialog is used to sort the
groups themselves. For example, you might group by city but then sort the
cities by the population. The table sort is used to sort the details. Since
the sort key might be composite, we have to support multiple fields in the
dialog.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee" <leemsrs@.hotmail.com> wrote in message
news:1114441358.314151.237870@.f14g2000cwb.googlegroups.com...
> Knowing where to apply the sort is the confusing part for me. There is
> a Sorting tab on the Table Properties dialog (my report consists of a
> single table whose parent is the report body). There is also a Sorting
> tab on the Grouping and Sorting Properties dialog (which can be
> accessed for each group). What are these two different Sorting tabs
> used for? Tell me if I got it right in the following example: the
> report my users want should be grouped by Field A, and then within each
> Field A group it should be grouped by Field B. Finally, within each
> Field B group it should be sorted (not grouped) by Field C. On the
> Grouping and Sorting Properties dialog for the Field A group, I went to
> the Sorting tab and chose Fields!FieldA.value. Next, on the Grouping
> and Sorting Properties dialog for the Field B group, I went to the
> Sorting tab and chose Fields!FieldB.value. Finally, on the Table
> Properties dialog, I went to the Sorting tab and chose
> Fields!FieldC.value as the only sort field. This seems to produce the
> grouping and sorting that my users want, but because I find the process
> counter-intuitive (my 4 years of previous reporting experience have
> been with Cognos Impromptu), I'm wondering if what I did is correct.
> Can you tell me?
>|||In your example, if we grouped by city but didn't sort by it, what
would happen? I would hope that whatever city was encountered first in
the dataset would appear first in the report, and all rows for that
city (no matter where they were scattered throughout the dataset) would
appear together on the report (i.e., no other cities in between them).
For example, consider a dataset with 10 rows. If there were 5 rows for
Boston at the beginning of the dataset, followed by 3 rows for Atlanta,
followed by another 2 rows for Boston, then the report would show the
Boston group first with all 7 rows followed by the Atlanta group with 3
rows. Is this correct?

Does 'Group By' affect the query speed?

i have a table such sa below:

Name1, Name2, Name3, Nam4, C1, C2,.., C100

and in this table, i have found index for Name1-Nam4,

i don't why sql below is very slow?

select
Name1, sum(C1), ...., Sum(C100)
from
(
select
Name1, Name2, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, Name4, C1, ...., C100
from
My_Table
group by Name1, Name2, Name3, Name4
) as T
group by Name1, Name2, Nam3
) as T
group by Name1, Name2
) as T
group by Name1

Does 'Group By' affect the speed of query?

Yes... It depends with your number of data...

I found your query is strange...

Why not, can you try the following query..

select
Name1, sum(C1), ...., Sum(C100)
from
My_Table
group by Name1

Bcs.. finally you are going to get only the Name1 data...

If you need kind of Rolling up data... use ROLLUP instead of multiple Subqueries...

Does anyone from MSFT monitor this forum anymore?

Most if not all other forums that I participate in include responses from people in QA and or the respective product group.... Several posts here have gone unresolved for weeks and I was curious as to why someone from MSFT hasn't attempted to respond. Thre are times when the only answer or resolution to a question or problem require inside information.

Yes, definitely MS is watching this group. But I guess there is no escalation for the threads in the groups within Microsoft, because this is a public group from MSDN.

BTW: Does anyone know or perhaps the person for himself know the name of a SQL Server guy at Microsoft whose name sounds something like SOUNDEX(Ted or Ed Lehman or Ehland) ? I got a mail from him some time ago but don′t have this mail due to crashes anymore. I would be glad if someone could point me in the right direction.

-Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Chris,

Your complaint duly noted. We've been a little busy with shipping Service Pack 1 but now that it's almost out we'll go through the recent posts and answer what we can.

Jens,

I contacted Ed Lehman and either he will reply here shortly or you can contact me directly (arturl at microsoft dot com). (Spammers, you know...)

Artur Laksberg
SQL Server Team
Microsoft

Document Map - Plz Help

I want to hide Duplicates in The Document Map in SQL Server 2000
Reporting Services.
I had group in document map like
- A
abc
- A
pqr
- A
lmn
+ B
+ B
+ B
+ B
+ B
+ C
+ C
...
but I want it to be
+ A
+ B
+ C
Could anyone help for resolving this problem.
Thanks & Best Regards
MukeshSomething to do with your data because RS does it properly by selecting each
group and sub group. In your case group is 'A', 'B' etc.. and subgroup is
'abc', 'pqr' etc.. So you need to check your subgroup data,
check that you have the grouping order properly selected. ie first Group and
then subgroup...
Amarnath
"mukesh" wrote:
> I want to hide Duplicates in The Document Map in SQL Server 2000
> Reporting Services.
> I had group in document map like
> - A
> abc
> - A
> pqr
> - A
> lmn
> + B
> + B
> + B
> + B
> + B
> + C
> + C
> ...
> but I want it to be
> + A
> + B
> + C
> Could anyone help for resolving this problem.
> Thanks & Best Regards
> Mukesh
>

Do Windows Authentication SQL 2k5 Logins survive a sysprep?

Hi,
Im not sure what group to ask this in, but this one seems the most
appropriate. I am responsible for "imaging" all of our developer machines
at my company. What this means is that we create a "master image", install
all appropriate software on it, configure it with correct options, and run
the sysprep tool which creates a "cloneable" disk image.
Then this disk image is basically blasted onto all the boot disks of the
company, and a mini-setup program is run, requiring a support person to type
the product key, etc.
I would like to create a few groups on this master image, do a CREATE LOGIN
FROM WINDOWS on them, give them appropriate permissions.
Will these logins and permissions survive the cloning operation? My biggest
concern would be how SQL refers to logins, but some kind of GUID, or by the
name itself. My guess is that if it is by name itself, then it should be
fine.
In case you are concerned about licensing, I am using SQLExpress :-)
Thanks for any insight,
JoeHi, Joe,
SQL Server recognize logins by the name. You can see the logins from the
system view sys.syslogins.
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Charles,
then why do a dba needs to fix the user when he/she restores the database to
a server where there is a same login exist with the same user name?
I thought even though they match it does not work because they have 2
different SID's.
Correct me if I am wrong, thanks...
Good day,
Bulent
"Charles Wang[MSFT]" wrote:

> Hi, Joe,
> SQL Server recognize logins by the name. You can see the logins from the
> system view sys.syslogins.
> If you have any other questions or concerns, please feel free to let me
> know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi, Bulent,
A DBA does not need to fix the users if he only wants to restore a database
to a new server.
Your thinking is right. Logins are associated to users by the security
identifier (SID), and if the SID is inconsistent after you move a database,
SQL Server may deny the user access to the database. At this time, you may
need to run the stored procedure sp_change_users_login to map the user and
login. Also there is a tool Mapsids.exe which can help you map SIDs between
user and master database when database is moved.
Please refer to:
How to move databases between computers that are running SQL Server
http://support.microsoft.com/?id=314546
How to resolve permission issues when you move a database between servers
that are running SQL Server
http://support.microsoft.com/kb/240872/
Please feel free to let me know if you have any other questions or
concerns. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles
This procedure has to be done only if you use SQL Server Authentication. If
you use Windows Authentication Only and have the same login exists on the
destination Server always should work properly on restored database.
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:y70UDz%23fHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Bulent,
> A DBA does not need to fix the users if he only wants to restore a
> database
> to a new server.
> Your thinking is right. Logins are associated to users by the security
> identifier (SID), and if the SID is inconsistent after you move a
> database,
> SQL Server may deny the user access to the database. At this time, you
> may
> need to run the stored procedure sp_change_users_login to map the user and
> login. Also there is a tool Mapsids.exe which can help you map SIDs
> between
> user and master database when database is moved.
> Please refer to:
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/?id=314546
> How to resolve permission issues when you move a database between servers
> that are running SQL Server
> http://support.microsoft.com/kb/240872/
> Please feel free to let me know if you have any other questions or
> concerns. Have a good day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>|||Hi Uri,
Thanks for your pointing out.
I have not found any official documents talking about this. Could you
please let me know why you have this idea?
What is the result if I manually add some domain accounts to a SQL Server
instance? If I run "select * from sys.syslogins", I can see the SIDs of the
new added domain accounts. Do you think that there are some differences
between a SQL account and a Windows account in this case?
Look forward to your response.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Charles
(SQL Server 2000 SP3)
We had recently some issues with moving databases from one server to
another. Lets assume SQL Server is running under SQL Server Autherntication.
The both servers have SQL Login 'Joy' and user database called also 'Joy' on
the source server . Now , we backup and restored database on the new server
so those SID does not match between SQL Login and User in the new restored
database , as a result you cannot connect to the DB.
It does not happen when you have Windows Authentication Only. The chain
does not break in new restored database and you can succefuly connect to the
DB.
I have not played with it on SQL Server 2005 so just my two cents :-))
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:fiA9ISKgHHA.4692@.TK2MSFTNGHUB02.phx.gbl...
> Hi Uri,
> Thanks for your pointing out.
> I have not found any official documents talking about this. Could you
> please let me know why you have this idea?
> What is the result if I manually add some domain accounts to a SQL Server
> instance? If I run "select * from sys.syslogins", I can see the SIDs of
> the
> new added domain accounts. Do you think that there are some differences
> between a SQL account and a Windows account in this case?
> Look forward to your response.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>