Hello,
I've got suprised that I had to change the code for a sample of my code,
upgrading from 2k to 2k5...
I have something similar with this code, which give me also different
results (of course, supposing that I have 2 identical Dbs, one in 2k and
another identical in 2k5):

declare @SumID bigint

set @SumID = 0
select @SumID = @SumID + c.id
from    syscolumns c
join    systypes t on c.xtype = t.xtype
order by c.id

SELECT    SumID = @SumID, [RowCount] = @@rowcount


Going deeper, I found strange that on 2k5 the following code returns 2
different results - recordsets (the only difference is ORDER BY clause), but
on 2k it returns 2 same results.

declare @SumID bigint

-- without order by
set @SumID = 0
select @SumID = @SumID + sign (c.id)
from    syscolumns c
join    systypes t on c.xtype = t.xtype

SELECT    SumID = @SumID, [RowCount] = @@rowcount

-- with order by
set @SumID = 0
select @SumID = @SumID + sign (c.id)
from    syscolumns c
join    systypes t on c.xtype = t.xtype
order by c.id

SELECT    SumID = @SumID,    [RowCount] = @@rowcount


I expected that it will return the SUM (sign(c.id)), but with ORDER BY it
evaluates only the last row!
In practice my code is a little bit more complex and I need it with order by!
Do I missing something? Is it a bug or whatelse?
Thanks for any help!

syscolumns is a table on SQL2000 and ID is an integer but it seems to have
only poisitive values.

syscolumns is a view in SQL2005 and ID seems to have negative values as well.

I hope it helps.

Danut :-)

Show quoteHide quote

Thanks Danut, but in my real case I don't use system tables / views. This
example use system tables (because I didn't want to create / fill some user
tables) but I want only to show that ORDER BY changes the results (the second
code example) and this affects my code.

Thanks again

Show quoteHide quote

Is the collation setting same on both SQL Server 2000 and 2005 for the
database? Try using the COLLATE clause in your select statements.

Please let me know if it make any difference.

Show quote Hide quote Arguably the only problem is that SQL Server never rejected your original
code as unreliable. The behaviour of a variable assignment in a SELECT
statement that returns multiple rows is undefined. In other words you can't
always predict what the result will be (even in SQL Server 2000 in fact).

IMO this syntax should be illegal. You certainly shouldn't depend on it
between versions because undocumented behaviour is subject to change without
warning. Instead do:

SET @sumid =
(SELECT SUM(id)
  FROM ...)

Thanks David,
Honestly, I never considered that this kind of use of the SELECT @Var =
expression (@Var) with multiple rows is a backdoor or should be consider
illegal !!!
I used it many times in the last 4 years with gaining performance, in places
where I had to have many table scans, and I reduced them to only one scan.
Neither the SQL 2k5 BOL is not very clear about this use of variable
assignement...

On "SELECT" we find:
<If the SELECT statement returns more than one value, the variable is
assigned the last value that is returned.>
Maybe we should understand: If the SELECT statement returns more than one
ROW? And how to determine the LAST VALUE THAT IS RETURNED (not for the last
row, but the last value!!!) if this value depends of the previous one?
Until date SQL2000 everytime evauluated it correctly (at least in my cases)

On "Expression" we find:
Expressions in a Transact-SQL select list follow a variation on this rule:
The expression IS EVALUATED INDIVIDUALLY FOR EACH ROW in the result set

I don't have SQL2000 BOL at the hand to look there, but before start using
this method (few years ago) I looked there and I understood that it correctly
support it.

Anyway... that's it
Thanks again


Show quoteHide quote

David Portas (REMOVE_BEFORE_REPLYING_dpor***@acm.org) writes:
It can't be made illegal, since it would break a lot of code out there.
But as long as the behaviour is undefined a warning would indeed be in
place. (It would be nice it the behaviour became defined and
actually was the result one would expect. It would certanily be useful.)

Catalin NASTAC (CatalinNAS***@discussions.microsoft.com) writes:
It's undefined behaviour, so you get what you get.

See http://support.microsoft.com/?kbid=897284, and pay particular
attention to the first paragraph under CAUSE.

I would strongly recommend you to redesign your solution to something
which is known to work.

Hi, Erland

You posted a link to the wrong KB article.

Razvan

I'm guessing Erland meant this one:

http://support.microsoft.com/kb/287515/

   Cause (of unexpected aggregate concatenation results)
   The correct behavior for an aggregate concatenation query is undefined.

I think it's fair to assume that the correct behavior of any aggregate query
that
uses variables to aggregate is likewise undefined.

SK

Show quoteHide quote

Thanks to all.
Steve,that article said:
"An examination of the SHOWPLAN output of the query reveals that the SQL
Server query processor builds an different execution plan WHEN EXPRESSIONS
ARE APPLIED TO COLUMNS IN THE QUERY'S ORDER BY CLAUSE, than when those same
expressions are applied to columns in the query's SELECT list."

but I also tried, for example

set @SumID = 0
select @SumID = @SumID + sign (c.ID)
from    syscolumns c
join    systypes t on c.xtype = t.xtype
order by c.NAME

I don't use the same columns and I get "the same different" results as when
used ID in SELECT and ORDER BY.

Seems that I will have to accept to modify my code.
Actually my code want to find the user rights based on a pirority table. A
user ca be member of many groups, let's say the groups are sorted by priority:
I have a table like this:

GroupName    SEL    INS    DEL    UPD
----------------------------------------------
Gr1        NULL    NULL    NULL    1
Gr2        1    1    NULL    NULL
Gr3        0    NULL    0    0


1 means acces, 0 deny, NULL not defined.
Starting from top we will take the first not null value and the result will
be:   
        1    1    0    1
I made this using
select    @select    = isnull (@select, Sel),
    @upd    = isnull(@upd, upd)
....
and so on
from    rights
order by GroupName


Is it any way but to not query several times?
I don't like the idea:
select top 1 @sel = sel from rights order by GroupName where sel is not null
select top 1 @upd = upd from rights order by GroupName where upd is not null
and so on...

Thanks

Catalin NASTAC (CatalinNAS***@discussions.microsoft.com) writes:
Show quoteHide quoteYou could set up a cursor, and iterate over the cursor, as long
as any of the variables are NULL. This could reduce the number of
access a little.

How may rights do you have? If they are only four, it may be manageable
to run it like this, but if there are a whole lot more of them, you
should probably make the rights rows instead of columns.

Razvan Socol (rso***@gmail.com) writes:
Oops! A copy/paste error somehow. Thanks to Steve for posting the correct
link.

What is this called, and how to do it?
SQL Server vs the Competition
Date Only Data Type
Select Records which are not present in second table
Retreiving multiple Output Parameters