concatenate - maybe?

I'm not sure what this process would be called.
I've got 2 columns...
[A] = FamilyName
[B] = FamilyMembers
I'm trying to make this...
[A] [B]
Doe Alan
Doe Bob
Doe Betty
Doe Joe
...into results like this...
[A] [B]
Doe Alan, Bob, Betty, Joe
i.e. Group column [B] into one field
thanksThis is called violating First Normal Form (1NF). It is a TOTAL
VIOLATION of the *most fundamental* principles of RDBMS.
Newbies without any business writing a database often post this request
in Newsgroups to show that they have never learned BASIC RDBMS
principles.
But even before SQL and RDBMS, the *most fundamental* principle of
*any* tiered architecture is that display is done in the client and
NEVER in the server. If you do not know this, then you should not be
programmng at all.|||Who the hell crapped in your soup?
Listen up a**hole!
There is NOT ONE LAW prohibiting me from joining the data into a query per
my preference!
And if this is the only way you can respond in a newsgroup, you need to move
on!
It is neither helpful, informative or appreciated.
Next time you run across a thread that pisses you off, move on!
You might ask yourself what would compel you to respond in such a manner!
No thanks!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130366077.636225.104700@.f14g2000cwb.googlegroups.com...
> This is called violating First Normal Form (1NF). It is a TOTAL
> VIOLATION of the *most fundamental* principles of RDBMS.
> Newbies without any business writing a database often post this request
> in Newsgroups to show that they have never learned BASIC RDBMS
> principles.
> But even before SQL and RDBMS, the *most fundamental* principle of
> *any* tiered architecture is that display is done in the client and
> NEVER in the server. If you do not know this, then you should not be
> programmng at all.
>|||Using the article http://www.aspfaq.com/show.asp?id=2529 as a basis the
solution below should help you out:
CREATE TABLE dbo.family
(
FamilyName VARCHAR(20),
FamilyMembers VARCHAR(20)
)
INSERT family SELECT 'Doe', 'Alan'
INSERT family SELECT 'Doe', 'Bob'
INSERT family SELECT 'Doe', 'Betty'
INSERT family SELECT 'Doe', 'Joe'
CREATE FUNCTION dbo.GetFamily
(
@.familyName VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.r VARCHAR(8000)
SELECT @.r = ISNULL(@.r+',', '') + familymembers
FROM dbo.family
WHERE familyname = @.familyName
RETURN @.r
END
GO
SELECT FamilyName, dbo.GetFamily(FamilyName)
FROM (SELECT familyname
FROM family
GROUP BY familyname) As a
- Peter Ward
WARDY IT Solutions
"shank" wrote:

> I'm not sure what this process would be called.
> I've got 2 columns...
> [A] = FamilyName
> [B] = FamilyMembers
> I'm trying to make this...
> [A] [B]
> Doe Alan
> Doe Bob
> Doe Betty
> Doe Joe
> ...into results like this...
> [A] [B]
> Doe Alan, Bob, Betty, Joe
> i.e. Group column [B] into one field
> thanks
>
>|||> But even before SQL and RDBMS, the *most fundamental* principle of
> *any* tiered architecture is that display is done in the client and
> NEVER in the server. If you do not know this, then you should not be
> programmng at all.
What absolute rubbish, you expose your lack of programming and real world
experience.
Formatting (what you term display) is done where it is most efficient to do
it, that may well be on the SELECT statement in the database or through
logic i.e. multiple row to single row conversion, again, within the
database.
Your ideas are 15 years old and way out of date.

> Newbies without any business writing a database often post this request
> in Newsgroups to show that they have never learned BASIC RDBMS
> principles.
You are not as respective nor experienced as you think you are.
You may also want to go back and fix this article where you have done a
fundemental mistake in not testing your design...
http://www.dbazine.com/ofinterest/oi-articles/celko14
From Kurt Sune's post of 26 Oct 2005, 14:21...
Tryed it in SQL server, doesnt work due to the fact that SQL server doesnt
treat count as sum.
This query gives the wrong answer, the usage of count taken from the
Celko-article.
SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
This one gives the right answer:
SELECT sum(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
,sum(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130366077.636225.104700@.f14g2000cwb.googlegroups.com...
> This is called violating First Normal Form (1NF). It is a TOTAL
> VIOLATION of the *most fundamental* principles of RDBMS.
> Newbies without any business writing a database often post this request
> in Newsgroups to show that they have never learned BASIC RDBMS
> principles.
> But even before SQL and RDBMS, the *most fundamental* principle of
> *any* tiered architecture is that display is done in the client and
> NEVER in the server. If you do not know this, then you should not be
> programmng at all.
>|||Hi There,
Formatting (what you term display) is done where it is most efficient
to do
it, that may well be on the SELECT statement in the database or through
logic i.e. multiple row to single row conversion, again, within the
database.
Do you consider it (multiple row to single row conversion) fast
/Efficient ? Formatting should (sorry must ) be done on Client Side
.Send the ordered output to the client and process the rows in
single-level-break report manner.
We are all here to use the newsgroup not to abUSE it or flame someone.
Who the hell crapped in your soup?
Listen up a**hole!
There is NOT ONE LAW prohibiting me from joining the data into a query
per my preference!
The newsgroup is like a street shop where you pick those things which
suits you or you like ,ignore whatever you feel not good/worth.
With Warm regards
Jatinder Singh|||Jatinder,
Your answers seem to have got lost in my post.

> Do you consider it (multiple row to single row conversion) fast
> /Efficient ? Formatting should (sorry must ) be done on Client Side
> .Send the ordered output to the client and process the rows in
> single-level-break report manner.
The point i'm trying to get across is that you need do it where its most
efficient to do it, you just can't make a definitive statement that
formatting must be done in the client.
What if there where a few thousand rows being concatenated?
You need to think things through, there is usually a network in place
between the SQL Server and the client, you need to consider scalability what
ever solution you decide to use.
I prefer to do things on and in the SQL Server because its central, i won't
have that scalability problem and now with SQL Server 2005 having CLR
integration i can do a lot of stuff more efficiently.

> We are all here to use the newsgroup not to abUSE it or flame someone.
Completely agree, you are talking to the wrong person here.
If you check my posts you will see the only person i talk down to is Celko
because of his arrogant attitude and rudeness to people who use this
community. You will also note a lot of other people do the same.
Being rude and arrogant to grow name popularity just to sell a book is not
what these communities are for which is what really gets my goat.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1130396709.846350.275340@.g47g2000cwa.googlegroups.com...
> Hi There,
> Formatting (what you term display) is done where it is most efficient
> to do
> it, that may well be on the SELECT statement in the database or through
> logic i.e. multiple row to single row conversion, again, within the
> database.
>
> Do you consider it (multiple row to single row conversion) fast
> /Efficient ? Formatting should (sorry must ) be done on Client Side
> .Send the ordered output to the client and process the rows in
> single-level-break report manner.
> We are all here to use the newsgroup not to abUSE it or flame someone.
> Who the hell crapped in your soup?
> Listen up a**hole!
> There is NOT ONE LAW prohibiting me from joining the data into a query
> per my preference!
> The newsgroup is like a street shop where you pick those things which
> suits you or you like ,ignore whatever you feel not good/worth.
>
> With Warm regards
> Jatinder Singh
>|||Hi Tony,
Thanks for your input , but using scalar function to produce the
concatenated output even for 100 or 1000 rows is not advisable ( I used
the term advisable ; because basic rules help us to write good and
managable code [sorry queries] )
If someone wish to use scalar function there is no one stopping him
or her , but in case the length of concated string crosses 8000 ,Isnot
it the output is incorrect?
SQL Server give us Inline and Scalar function but I really donot
use any of them . What I feel is scalar function can be replaced by
formula directly and an INline function can be replaced by a view with
proper WHERE Clause .So ,where does a function really fit in?
With Warm regards
Jatinder Singh|||It depends what you are doing, if you want concatenated output then yes you
need to consider the 8000 byte limit for a scalar function, alternatively
use a table variable.
This all becomes significantly better and more performant in SQL Server 2005
with CLR, also the TSQL functions are faster as well.
Say you are concatenating 20 values, having 20 joins starts to become
unmanageable and difficult to maintain.
You might be concatenating them for good reason, for instance a message
board thread just like this one. There are tons of other reasons too.
I think i'll finish with a call back to my original post - you cannot
definitively state not to do stuff in SQL Server, it depends what you are
doing - you must test each method and make sure it scales!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1130416953.071865.8500@.f14g2000cwb.googlegroups.com...
> Hi Tony,
> Thanks for your input , but using scalar function to produce the
> concatenated output even for 100 or 1000 rows is not advisable ( I used
> the term advisable ; because basic rules help us to write good and
> managable code [sorry queries] )
> If someone wish to use scalar function there is no one stopping him
> or her , but in case the length of concated string crosses 8000 ,Isnot
> it the output is incorrect?
> SQL Server give us Inline and Scalar function but I really donot
> use any of them . What I feel is scalar function can be replaced by
> formula directly and an INline function can be replaced by a view with
> proper WHERE Clause .So ,where does a function really fit in?
> With Warm regards
> Jatinder Singh
>|||I am appalled at the arrogance of this. I have been designing and building
databases since 1978 and Hierarchical and Codasyl, I then moved on to Relati
onal and NO relational fits all the rules of Codd.
What a piece of arrant nonsense. What this 'CELKO' person does not realise
is that RDBMS is itself a cludge.
I noticed a complete absence of creative input from 'CELKO'.
What this person is doing here is stifling innovation. As other posts sai
d, what if there are thousands of rows.
The ART of database design is in initially normalizing, and THEN de-normaliz
ing for efficiency. Retaining NF for the sake of NF is mentally pathetic.
I have successfully built many very large databases, and many small ones, I
have designed my own DBMS, and worked closely with the architects of some o
f the world's most important DBMSs, you sir, are talking out of your hat.
As for the original post, good question, but I too think you will be stymied
by the 8k limit.
Jerry

quote:

---

Originally posted by --CELKO--
This is called violating First Normal Form (1NF). It is a TOTAL
VIOLATION of the *most fundamental* principles of RDBMS.
Newbies without any business writing a database often post this request
in Newsgroups to show that they have never learned BASIC RDBMS
principles.
But even before SQL and RDBMS, the *most fundamental* principle of
*any* tiered architecture is that display is done in the client and
NEVER in the server. If you do not know this, then you should not be
programmng at all.

---

composing a reference from fields located in mutiple tables

Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation
(assignment->document.attribute(s) + assignment.date) -> pricelist.price

Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.

Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?

Not quite sure I understand the entire request. However, it seems you should be able to enforce referential integrity via Instead Of trigger.

Perhaps, you could give us some sample DDL and desired output to better describe your issue. We might be able to help further then.

|||Normally, you have all the tables interrelated. The reference (a foreign key) points to an object in another table specifying the "container" it belongs to. For instance, many books refer a single author.

Sometimes, you need to establish a complex reference consisting of multiple fields. For instance, a job refers to pricelist. The options in the print job specify a "service id", which has a unique price in the pricelist.

Suppose now that the pricelist can be updated. When job is created, it fixes the latest service cost in int field, the pricelist date. So the cost is uniquely identified by the job options (some fields) and the date. This is a complex key.

What I have faced is that nobody addresses the possibility of having the job attributes fixed in a separate table (say, documents to be processed always have the same settings). A job refers a document, from which the attributes are derived and coupled with the pricelist date identifies the job cost in the pricelist table. Effectively, the complex key is composed from fields located in different tables. A record contains only a part of complex key plus a reference to another entity, which has a rest of the key.

One way to create a relation would be to produce a view joining the key field tables. However, views are not enabled in diagrams. I suppose the reason is because the views are not allowed to participate in data relations.

Actually, I have decided that in my case I do not need to fix the job settings in the referred document, so I'll have all the key fields in one table. Yet, the topic is quite general to be interesting for me and others.

Complicated IIF statements

We have some conditions that are required to be met to process the calculations. For the IIF , we have two conditions that are required to be true and also for two different fields.

There is a field called MEASURE which should be either "Discharges" OR "Panel Discharges".

There is another field called fiscal year which should be "TFY"

If the above conditions are met, then we want to SUM(Fields!Value.Numerator) but if the test fails then we want (Fields!Numerator.Value)/(Fields!NDenominator.Value)

I have tried :

=IIF(Fields!MEASURE.Value ="Panel Discharges", SUM(IIF(Fields!FiscalYear IS "TFY", Fields!Numerator.Value),(Fields!Numerator.Value)/(Fields!NDenominator.Value))

I tried other style of writing but failed.

Any help is appreciated!

I think there are two issues:
1. the outer IIF function call has only two arguments instead of the required three arguments
2. the conditional SUM aggregate can trigger a division by zero

I recommend to add a custom code function for the division (in Report -> Report Properties -> Code):

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

Then, modify the expression accordingly:

=IIF( (Fields!MEASURE.Value="Panel Discharges") OR (Fields!MEASURE.Value="Discharges"), SUM(IIF(Fields!FiscalYear="TFY", Fields!Numerator.Value, Code.Divide(Fields!Numerator.Value, Fields!NDenominator.Value))), 0)

-- Robert

Comparision test SQL 7.0 and 2000

I have a 6.5 compatibility mode database that is running on SQL Server 7.0.
I'm in the process of doing a compatibility test on files generated from the
same database on SQL 2000 (which is also running in 6.5 compatibility mode -
I copied it from the 7.0 server using the 2000 copy database wizard). I'm
getting different results when I create a file against test data, and don't
understand why. The file is generated from a query. The differences in the
file seem to be due to how the DirActivityLog sorts when it is matched.
The query is:
declare @.start as datetime,
@.stop as datetime
Select @.start = '03/15/05 00:01:00', @.stop = '04/15/05 00:01:00'
Select Dir.DirCode, Dir.PubCode, Dir.CountryCode,
Dir.StateCode, Dir.DirNameFull, Dir.DirNameAbbr,
Dir.FocusCode, Dir.SubCatCode, Dir.SectionFlag, dir.Action,
da.ActivityCode
from RDHistory..Directory dir (NOLOCK), DirActivityLog da (NOLOCK)
where dir.BatchRcvdDate between @.start and @.stop
and dir.CompleteFlag = 1 and
dir.DirCode *= da.DirCode and
dir.BatchRcvdDate *= da.ActivityDate
order by dir.DirCode, dir.HistID
The data contains 2 records in RDHistory..Directory for dircode 116004,
BatchRcvdDate = '3/29/05 7:00:00 AM' - the first with histid 35097 and actio
n
'U", and the second with histid 35098 and action 'D'.
DirActivityLog contains 2 records for the same dircode 116004, with
ActivityDate = '3/29/05 7:00:00 AM' - the first record with Logid 47563 and
ActivityCode = 'N' and the second with Logid 47564 and ActivityCode 'D'.
On SQL 7.0, I get the following 4 rows with this in the DirCode, ActionCode
and ActivityCode fields:
116004,U,N
116004,U,D
116004,D,N
116004,D,D
On SQL 2000, I get the following 4 rows with this in the DirCode, ActionCode
and ActivityCode:
116004,U,N
116004,U,D
116004,D,D
116004,D,N
This changes the results written out to the end file.
The execution plans on both servers seem to be the same. The code page and
sortid used on SQL 7.0 seems compatibile with the collation used on SQL 2000
.
I can solve this problem by adding more sort fields to the query, but I'd
rather not modify the application if I can help it - and I don't know why
this is occuring.
Help!SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
news:FD582374-795E-4AF6-902F-9C5F76F9561A@.microsoft.com...
>I have a 6.5 compatibility mode database that is running on SQL Server 7.0.
> I'm in the process of doing a compatibility test on files generated from
> the
> same database on SQL 2000 (which is also running in 6.5 compatibility
> mode -
> I copied it from the 7.0 server using the 2000 copy database wizard). I'm
> getting different results when I create a file against test data, and
> don't
> understand why. The file is generated from a query. The differences in
> the
> file seem to be due to how the DirActivityLog sorts when it is matched.
> The query is:
> declare @.start as datetime,
> @.stop as datetime
> Select @.start = '03/15/05 00:01:00', @.stop = '04/15/05 00:01:00'
> Select Dir.DirCode, Dir.PubCode, Dir.CountryCode,
> Dir.StateCode, Dir.DirNameFull, Dir.DirNameAbbr,
> Dir.FocusCode, Dir.SubCatCode, Dir.SectionFlag, dir.Action,
> da.ActivityCode
> from RDHistory..Directory dir (NOLOCK), DirActivityLog da (NOLOCK)
> where dir.BatchRcvdDate between @.start and @.stop
> and dir.CompleteFlag = 1 and
> dir.DirCode *= da.DirCode and
> dir.BatchRcvdDate *= da.ActivityDate
> order by dir.DirCode, dir.HistID
> The data contains 2 records in RDHistory..Directory for dircode 116004,
> BatchRcvdDate = '3/29/05 7:00:00 AM' - the first with histid 35097 and
> action
> 'U", and the second with histid 35098 and action 'D'.
> DirActivityLog contains 2 records for the same dircode 116004, with
> ActivityDate = '3/29/05 7:00:00 AM' - the first record with Logid 47563
> and
> ActivityCode = 'N' and the second with Logid 47564 and ActivityCode 'D'.
> On SQL 7.0, I get the following 4 rows with this in the DirCode,
> ActionCode
> and ActivityCode fields:
> 116004,U,N
> 116004,U,D
> 116004,D,N
> 116004,D,D
> On SQL 2000, I get the following 4 rows with this in the DirCode,
> ActionCode
> and ActivityCode:
> 116004,U,N
> 116004,U,D
> 116004,D,D
> 116004,D,N
>
This is expected. Your ORDER BY does not impose a complete ordering on the
results. The first two records have HistID=35097 and the second two have
HistID=35098. Beyond that, the order is unpredictable.
You have asked the server to "order by dir.DirCode, dir.HistID", and it is.
But since multiple rows in the result can share the same (DirCode,HistID),
you have also told the server that you do not care about the ordering beyond
DirCode and HistID. Unordered rows are returned an a order which is an
accident of the implementation of the query execution. The order of these
rows was never guaranteed in SQL 6.5, and so there is no flag to force SQL
2000 to reproduce the order from 6.5.
You must decide how you want the results sorted, and force the sort order by
adding appropriate columns to the ORDER BY.
David|||"David Browne" wrote:

> You have asked the server to "order by dir.DirCode, dir.HistID", and it is
.
> But since multiple rows in the result can share the same (DirCode,HistID),
> you have also told the server that you do not care about the ordering beyo
nd
> DirCode and HistID. Unordered rows are returned an a order which is an
> accident of the implementation of the query execution. The order of these
> rows was never guaranteed in SQL 6.5, and so there is no flag to force SQL
> 2000 to reproduce the order from 6.5.
> You must decide how you want the results sorted, and force the sort order
by
> adding appropriate columns to the ORDER BY.
> David
Thanks for your quick response. I understand your point, and I can
certainly modify the order by clause in the sql statement to enforce the
order I want. However, what I don't understand is why SQL Server 7.0 would
consistently implement the sql one way, and SQL Server 2000 would
consistently implement it in another. If I got random sort results on these
fields on both servers - that I would understand. Any idea of what's change
d?|||"SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
news:C6CFDE94-0F46-474B-A9AA-CF7B2ADB6181@.microsoft.com...
> "David Browne" wrote:
>
> Thanks for your quick response. I understand your point, and I can
> certainly modify the order by clause in the sql statement to enforce the
> order I want. However, what I don't understand is why SQL Server 7.0
> would
> consistently implement the sql one way, and SQL Server 2000 would
> consistently implement it in another. If I got random sort results on
> these
> fields on both servers - that I would understand. Any idea of what's
> changed?
It's not random. It's an accident of the implementation. Sql 2000 uses
different file structures and extensively revised and optimized
implementations of sorts and joins and whatnot. It just so happens that
these implementations return unsorted rows in a different order than SQL
6.5.
Imagine this conversation:
Programmer1: "Hey I can shave 0.05% from the CPU use for a hash join by
pushing the row pointers onto a temporary stack."
Programmer2: "But won't that reverse the order the rows are returned?"
Programmer1: "Yes but if the order is important, a subsequent ORDER BY step
will sort the rows."
Programmer2: "Cool."
David
David|||You're right - it's a moot point. Thanks again.
"David Browne" wrote:

> "SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
> news:C6CFDE94-0F46-474B-A9AA-CF7B2ADB6181@.microsoft.com...
> It's not random. It's an accident of the implementation. Sql 2000 uses
> different file structures and extensively revised and optimized
> implementations of sorts and joins and whatnot. It just so happens that
> these implementations return unsorted rows in a different order than SQL
> 6.5.
> Imagine this conversation:
> Programmer1: "Hey I can shave 0.05% from the CPU use for a hash join by
> pushing the row pointers onto a temporary stack."
> Programmer2: "But won't that reverse the order the rows are returned?"
> Programmer1: "Yes but if the order is important, a subsequent ORDER BY st
ep
> will sort the rows."
> Programmer2: "Cool."
> David
>
> David
>
>

Comparision test SQL 7.0 and 2000

I have a 6.5 compatibility mode database that is running on SQL Server 7.0.
I'm in the process of doing a compatibility test on files generated from the
same database on SQL 2000 (which is also running in 6.5 compatibility mode -
I copied it from the 7.0 server using the 2000 copy database wizard). I'm
getting different results when I create a file against test data, and don't
understand why. The file is generated from a query. The differences in the
file seem to be due to how the DirActivityLog sorts when it is matched.
The query is:
declare @.start as datetime,
@.stop as datetime
Select @.start = '03/15/05 00:01:00', @.stop = '04/15/05 00:01:00'
Select Dir.DirCode, Dir.PubCode, Dir.CountryCode,
Dir.StateCode, Dir.DirNameFull, Dir.DirNameAbbr,
Dir.FocusCode, Dir.SubCatCode, Dir.SectionFlag, dir.Action,
da.ActivityCode
from RDHistory..Directory dir (NOLOCK), DirActivityLog da (NOLOCK)
where dir.BatchRcvdDate between @.start and @.stop
and dir.CompleteFlag = 1 and
dir.DirCode *= da.DirCode and
dir.BatchRcvdDate *= da.ActivityDate
order by dir.DirCode, dir.HistID
The data contains 2 records in RDHistory..Directory for dircode 116004,
BatchRcvdDate = '3/29/05 7:00:00 AM' - the first with histid 35097 and action
'U", and the second with histid 35098 and action 'D'.
DirActivityLog contains 2 records for the same dircode 116004, with
ActivityDate = '3/29/05 7:00:00 AM' - the first record with Logid 47563 and
ActivityCode = 'N' and the second with Logid 47564 and ActivityCode 'D'.
On SQL 7.0, I get the following 4 rows with this in the DirCode, ActionCode
and ActivityCode fields:
116004,U,N
116004,U,D
116004,D,N
116004,D,D
On SQL 2000, I get the following 4 rows with this in the DirCode, ActionCode
and ActivityCode:
116004,U,N
116004,U,D
116004,D,D
116004,D,N
This changes the results written out to the end file.
The execution plans on both servers seem to be the same. The code page and
sortid used on SQL 7.0 seems compatibile with the collation used on SQL 2000.
I can solve this problem by adding more sort fields to the query, but I'd
rather not modify the application if I can help it - and I don't know why
this is occuring.
Help!
SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
news:FD582374-795E-4AF6-902F-9C5F76F9561A@.microsoft.com...
>I have a 6.5 compatibility mode database that is running on SQL Server 7.0.
> I'm in the process of doing a compatibility test on files generated from
> the
> same database on SQL 2000 (which is also running in 6.5 compatibility
> mode -
> I copied it from the 7.0 server using the 2000 copy database wizard). I'm
> getting different results when I create a file against test data, and
> don't
> understand why. The file is generated from a query. The differences in
> the
> file seem to be due to how the DirActivityLog sorts when it is matched.
> The query is:
> declare @.start as datetime,
> @.stop as datetime
> Select @.start = '03/15/05 00:01:00', @.stop = '04/15/05 00:01:00'
> Select Dir.DirCode, Dir.PubCode, Dir.CountryCode,
> Dir.StateCode, Dir.DirNameFull, Dir.DirNameAbbr,
> Dir.FocusCode, Dir.SubCatCode, Dir.SectionFlag, dir.Action,
> da.ActivityCode
> from RDHistory..Directory dir (NOLOCK), DirActivityLog da (NOLOCK)
> where dir.BatchRcvdDate between @.start and @.stop
> and dir.CompleteFlag = 1 and
> dir.DirCode *= da.DirCode and
> dir.BatchRcvdDate *= da.ActivityDate
> order by dir.DirCode, dir.HistID
> The data contains 2 records in RDHistory..Directory for dircode 116004,
> BatchRcvdDate = '3/29/05 7:00:00 AM' - the first with histid 35097 and
> action
> 'U", and the second with histid 35098 and action 'D'.
> DirActivityLog contains 2 records for the same dircode 116004, with
> ActivityDate = '3/29/05 7:00:00 AM' - the first record with Logid 47563
> and
> ActivityCode = 'N' and the second with Logid 47564 and ActivityCode 'D'.
> On SQL 7.0, I get the following 4 rows with this in the DirCode,
> ActionCode
> and ActivityCode fields:
> 116004,U,N
> 116004,U,D
> 116004,D,N
> 116004,D,D
> On SQL 2000, I get the following 4 rows with this in the DirCode,
> ActionCode
> and ActivityCode:
> 116004,U,N
> 116004,U,D
> 116004,D,D
> 116004,D,N
>
This is expected. Your ORDER BY does not impose a complete ordering on the
results. The first two records have HistID=35097 and the second two have
HistID=35098. Beyond that, the order is unpredictable.
You have asked the server to "order by dir.DirCode, dir.HistID", and it is.
But since multiple rows in the result can share the same (DirCode,HistID),
you have also told the server that you do not care about the ordering beyond
DirCode and HistID. Unordered rows are returned an a order which is an
accident of the implementation of the query execution. The order of these
rows was never guaranteed in SQL 6.5, and so there is no flag to force SQL
2000 to reproduce the order from 6.5.
You must decide how you want the results sorted, and force the sort order by
adding appropriate columns to the ORDER BY.
David
|||"David Browne" wrote:

> You have asked the server to "order by dir.DirCode, dir.HistID", and it is.
> But since multiple rows in the result can share the same (DirCode,HistID),
> you have also told the server that you do not care about the ordering beyond
> DirCode and HistID. Unordered rows are returned an a order which is an
> accident of the implementation of the query execution. The order of these
> rows was never guaranteed in SQL 6.5, and so there is no flag to force SQL
> 2000 to reproduce the order from 6.5.
> You must decide how you want the results sorted, and force the sort order by
> adding appropriate columns to the ORDER BY.
> David
Thanks for your quick response. I understand your point, and I can
certainly modify the order by clause in the sql statement to enforce the
order I want. However, what I don't understand is why SQL Server 7.0 would
consistently implement the sql one way, and SQL Server 2000 would
consistently implement it in another. If I got random sort results on these
fields on both servers - that I would understand. Any idea of what's changed?
|||"SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
news:C6CFDE94-0F46-474B-A9AA-CF7B2ADB6181@.microsoft.com...
> "David Browne" wrote:
>
> Thanks for your quick response. I understand your point, and I can
> certainly modify the order by clause in the sql statement to enforce the
> order I want. However, what I don't understand is why SQL Server 7.0
> would
> consistently implement the sql one way, and SQL Server 2000 would
> consistently implement it in another. If I got random sort results on
> these
> fields on both servers - that I would understand. Any idea of what's
> changed?
It's not random. It's an accident of the implementation. Sql 2000 uses
different file structures and extensively revised and optimized
implementations of sorts and joins and whatnot. It just so happens that
these implementations return unsorted rows in a different order than SQL
6.5.
Imagine this conversation:
Programmer1: "Hey I can shave 0.05% from the CPU use for a hash join by
pushing the row pointers onto a temporary stack."
Programmer2: "But won't that reverse the order the rows are returned?"
Programmer1: "Yes but if the order is important, a subsequent ORDER BY step
will sort the rows."
Programmer2: "Cool."
David
David
|||You're right - it's a moot point. Thanks again.
"David Browne" wrote:

> "SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
> news:C6CFDE94-0F46-474B-A9AA-CF7B2ADB6181@.microsoft.com...
> It's not random. It's an accident of the implementation. Sql 2000 uses
> different file structures and extensively revised and optimized
> implementations of sorts and joins and whatnot. It just so happens that
> these implementations return unsorted rows in a different order than SQL
> 6.5.
> Imagine this conversation:
> Programmer1: "Hey I can shave 0.05% from the CPU use for a hash join by
> pushing the row pointers onto a temporary stack."
> Programmer2: "But won't that reverse the order the rows are returned?"
> Programmer1: "Yes but if the order is important, a subsequent ORDER BY step
> will sort the rows."
> Programmer2: "Cool."
> David
>
> David
>
>

Comparision test SQL 7.0 and 2000

I have a 6.5 compatibility mode database that is running on SQL Server 7.0.
I'm in the process of doing a compatibility test on files generated from the
same database on SQL 2000 (which is also running in 6.5 compatibility mode -
I copied it from the 7.0 server using the 2000 copy database wizard). I'm
getting different results when I create a file against test data, and don't
understand why. The file is generated from a query. The differences in the
file seem to be due to how the DirActivityLog sorts when it is matched.
The query is:
declare @.start as datetime,
@.stop as datetime
Select @.start = '03/15/05 00:01:00', @.stop = '04/15/05 00:01:00'
Select Dir.DirCode, Dir.PubCode, Dir.CountryCode,
Dir.StateCode, Dir.DirNameFull, Dir.DirNameAbbr,
Dir.FocusCode, Dir.SubCatCode, Dir.SectionFlag, dir.Action,
da.ActivityCode
from RDHistory..Directory dir (NOLOCK), DirActivityLog da (NOLOCK)
where dir.BatchRcvdDate between @.start and @.stop
and dir.CompleteFlag = 1 and
dir.DirCode *= da.DirCode and
dir.BatchRcvdDate *= da.ActivityDate
order by dir.DirCode, dir.HistID
The data contains 2 records in RDHistory..Directory for dircode 116004,
BatchRcvdDate = '3/29/05 7:00:00 AM' - the first with histid 35097 and action
'U", and the second with histid 35098 and action 'D'.
DirActivityLog contains 2 records for the same dircode 116004, with
ActivityDate = '3/29/05 7:00:00 AM' - the first record with Logid 47563 and
ActivityCode = 'N' and the second with Logid 47564 and ActivityCode 'D'.
On SQL 7.0, I get the following 4 rows with this in the DirCode, ActionCode
and ActivityCode fields:
116004,U,N
116004,U,D
116004,D,N
116004,D,D
On SQL 2000, I get the following 4 rows with this in the DirCode, ActionCode
and ActivityCode:
116004,U,N
116004,U,D
116004,D,D
116004,D,N
This changes the results written out to the end file.
The execution plans on both servers seem to be the same. The code page and
sortid used on SQL 7.0 seems compatibile with the collation used on SQL 2000.
I can solve this problem by adding more sort fields to the query, but I'd
rather not modify the application if I can help it - and I don't know why
this is occuring.
Help!SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
news:FD582374-795E-4AF6-902F-9C5F76F9561A@.microsoft.com...
>I have a 6.5 compatibility mode database that is running on SQL Server 7.0.
> I'm in the process of doing a compatibility test on files generated from
> the
> same database on SQL 2000 (which is also running in 6.5 compatibility
> mode -
> I copied it from the 7.0 server using the 2000 copy database wizard). I'm
> getting different results when I create a file against test data, and
> don't
> understand why. The file is generated from a query. The differences in
> the
> file seem to be due to how the DirActivityLog sorts when it is matched.
> The query is:
> declare @.start as datetime,
> @.stop as datetime
> Select @.start = '03/15/05 00:01:00', @.stop = '04/15/05 00:01:00'
> Select Dir.DirCode, Dir.PubCode, Dir.CountryCode,
> Dir.StateCode, Dir.DirNameFull, Dir.DirNameAbbr,
> Dir.FocusCode, Dir.SubCatCode, Dir.SectionFlag, dir.Action,
> da.ActivityCode
> from RDHistory..Directory dir (NOLOCK), DirActivityLog da (NOLOCK)
> where dir.BatchRcvdDate between @.start and @.stop
> and dir.CompleteFlag = 1 and
> dir.DirCode *= da.DirCode and
> dir.BatchRcvdDate *= da.ActivityDate
> order by dir.DirCode, dir.HistID
> The data contains 2 records in RDHistory..Directory for dircode 116004,
> BatchRcvdDate = '3/29/05 7:00:00 AM' - the first with histid 35097 and
> action
> 'U", and the second with histid 35098 and action 'D'.
> DirActivityLog contains 2 records for the same dircode 116004, with
> ActivityDate = '3/29/05 7:00:00 AM' - the first record with Logid 47563
> and
> ActivityCode = 'N' and the second with Logid 47564 and ActivityCode 'D'.
> On SQL 7.0, I get the following 4 rows with this in the DirCode,
> ActionCode
> and ActivityCode fields:
> 116004,U,N
> 116004,U,D
> 116004,D,N
> 116004,D,D
> On SQL 2000, I get the following 4 rows with this in the DirCode,
> ActionCode
> and ActivityCode:
> 116004,U,N
> 116004,U,D
> 116004,D,D
> 116004,D,N
>
This is expected. Your ORDER BY does not impose a complete ordering on the
results. The first two records have HistID=35097 and the second two have
HistID=35098. Beyond that, the order is unpredictable.
You have asked the server to "order by dir.DirCode, dir.HistID", and it is.
But since multiple rows in the result can share the same (DirCode,HistID),
you have also told the server that you do not care about the ordering beyond
DirCode and HistID. Unordered rows are returned an a order which is an
accident of the implementation of the query execution. The order of these
rows was never guaranteed in SQL 6.5, and so there is no flag to force SQL
2000 to reproduce the order from 6.5.
You must decide how you want the results sorted, and force the sort order by
adding appropriate columns to the ORDER BY.
David|||"David Browne" wrote:
> You have asked the server to "order by dir.DirCode, dir.HistID", and it is.
> But since multiple rows in the result can share the same (DirCode,HistID),
> you have also told the server that you do not care about the ordering beyond
> DirCode and HistID. Unordered rows are returned an a order which is an
> accident of the implementation of the query execution. The order of these
> rows was never guaranteed in SQL 6.5, and so there is no flag to force SQL
> 2000 to reproduce the order from 6.5.
> You must decide how you want the results sorted, and force the sort order by
> adding appropriate columns to the ORDER BY.
> David
Thanks for your quick response. I understand your point, and I can
certainly modify the order by clause in the sql statement to enforce the
order I want. However, what I don't understand is why SQL Server 7.0 would
consistently implement the sql one way, and SQL Server 2000 would
consistently implement it in another. If I got random sort results on these
fields on both servers - that I would understand. Any idea of what's changed?|||"SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
news:C6CFDE94-0F46-474B-A9AA-CF7B2ADB6181@.microsoft.com...
> "David Browne" wrote:
>> You have asked the server to "order by dir.DirCode, dir.HistID", and it
>> is.
>> But since multiple rows in the result can share the same
>> (DirCode,HistID),
>> you have also told the server that you do not care about the ordering
>> beyond
>> DirCode and HistID. Unordered rows are returned an a order which is an
>> accident of the implementation of the query execution. The order of these
>> rows was never guaranteed in SQL 6.5, and so there is no flag to force
>> SQL
>> 2000 to reproduce the order from 6.5.
>> You must decide how you want the results sorted, and force the sort order
>> by
>> adding appropriate columns to the ORDER BY.
>> David
> Thanks for your quick response. I understand your point, and I can
> certainly modify the order by clause in the sql statement to enforce the
> order I want. However, what I don't understand is why SQL Server 7.0
> would
> consistently implement the sql one way, and SQL Server 2000 would
> consistently implement it in another. If I got random sort results on
> these
> fields on both servers - that I would understand. Any idea of what's
> changed?
It's not random. It's an accident of the implementation. Sql 2000 uses
different file structures and extensively revised and optimized
implementations of sorts and joins and whatnot. It just so happens that
these implementations return unsorted rows in a different order than SQL
6.5.
Imagine this conversation:
Programmer1: "Hey I can shave 0.05% from the CPU use for a hash join by
pushing the row pointers onto a temporary stack."
Programmer2: "But won't that reverse the order the rows are returned?"
Programmer1: "Yes but if the order is important, a subsequent ORDER BY step
will sort the rows."
Programmer2: "Cool."
David
David|||You're right - it's a moot point. Thanks again.
"David Browne" wrote:
> "SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
> news:C6CFDE94-0F46-474B-A9AA-CF7B2ADB6181@.microsoft.com...
> > "David Browne" wrote:
> >
> >> You have asked the server to "order by dir.DirCode, dir.HistID", and it
> >> is.
> >> But since multiple rows in the result can share the same
> >> (DirCode,HistID),
> >> you have also told the server that you do not care about the ordering
> >> beyond
> >> DirCode and HistID. Unordered rows are returned an a order which is an
> >> accident of the implementation of the query execution. The order of these
> >> rows was never guaranteed in SQL 6.5, and so there is no flag to force
> >> SQL
> >> 2000 to reproduce the order from 6.5.
> >>
> >> You must decide how you want the results sorted, and force the sort order
> >> by
> >> adding appropriate columns to the ORDER BY.
> >>
> >> David
> >
> > Thanks for your quick response. I understand your point, and I can
> > certainly modify the order by clause in the sql statement to enforce the
> > order I want. However, what I don't understand is why SQL Server 7.0
> > would
> > consistently implement the sql one way, and SQL Server 2000 would
> > consistently implement it in another. If I got random sort results on
> > these
> > fields on both servers - that I would understand. Any idea of what's
> > changed?
> It's not random. It's an accident of the implementation. Sql 2000 uses
> different file structures and extensively revised and optimized
> implementations of sorts and joins and whatnot. It just so happens that
> these implementations return unsorted rows in a different order than SQL
> 6.5.
> Imagine this conversation:
> Programmer1: "Hey I can shave 0.05% from the CPU use for a hash join by
> pushing the row pointers onto a temporary stack."
> Programmer2: "But won't that reverse the order the rows are returned?"
> Programmer1: "Yes but if the order is important, a subsequent ORDER BY step
> will sort the rows."
> Programmer2: "Cool."
> David
>
> David
>
>