July 2, 2018 at 9:30 pm
Comments posted to this topic are about the item Assignments
July 3, 2018 at 1:50 am
Nice one, thanks, Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
July 3, 2018 at 3:00 am
So simple. Amazing 32% can get it wrong!
July 3, 2018 at 7:43 am
It's less surprising that so many people get it "wrong" given that the official answer from Microsoft is that evaluation order in multiple assignments in a select statements is undefined.
July 3, 2018 at 7:49 am
I admit I took a guess at this one.
It was obviously going to be either 10, 10 (if the calculations were done in sequential order), or, if the calculations were done as a single "simultaneous" transaction from the starting values in each variable, it was going to be 10, 5.
However, I suppose it does make for more consistency that the effect of
SELECT @a = @b-2, @b-2 = @a;
is equivalent to
SELECT @a=@b; SELECT @b-2 = @a;
MarkD
July 3, 2018 at 8:00 am
lmalatesta - Tuesday, July 3, 2018 7:43 AMIt's less surprising given that the official answer from Microsoft is that evaluation order in multiple assignments in a select statements is undefined.
Oh, indeed! You don't say!
In that case, either (5, 5), (10, 10) or possibly (10,5) are possible "correct" answers.
How reassuring is that!
MarkD
July 3, 2018 at 8:22 am
lmalatesta - Tuesday, July 3, 2018 7:43 AM
In that case, either (5, 5), (10, 10) or possibly (10,5) are possible "correct" answers.
There are different levels of "correct". A large number of SQL Server professionals rely on all sorts of undocumented behavior. That Microsoft doesn't guarantee the order of assignments doesn't mean that the SQL Server engine doesn't operate in a certain way, only that it isn't designed to definitively operate in that certain way.
I wouldn't rely on this sort of assignment in production code - at least not for variables. But it may very well work well in a number of applications. It may even be as unlikely to have a different order than it is for two calls to NEWID() to have identical values. But, certainly, it's not guaranteed.
Note: this behavior also only works for variables. For column assignments in update statements, the SQL Server engine uses an "all at once paradigm" which means you can swap values in columns in a single update. But of course, that's the SET clause in and UPDATE statement not a variable assignment in a SELECT statement.
July 3, 2018 at 4:18 pm
lmalatesta - Tuesday, July 3, 2018 8:22 AMNote: this behavior also only works for variables. For column assignments in update statements, the SQL Server engine uses an "all at once paradigm" which means you can swap values in columns in a single update. But of course, that's the SET clause in and UPDATE statement not a variable assignment in a SELECT statement.
I definitely made the mistake of assuming that SELECT would behave similarly to an UPDATE statement. I know one can swap the primary keys on a pair of rows in a single UPDATE statement without upsetting foreign keys (a truly evil and sneaky trick) like so:UPDATE SomeTable
SET PKID = CASE WHEN PKID = 2 THEN 3 ELSE 2 END
WHERE PKID IN (2, 3);
That can be really ugly if you're trying to figure out which rows got updated in a trigger - pairing the inserted and deleted tables gets difficult if you can't trust the primary key values!
I try to avoid using SELECT to assign to variables because of the lack of single-row enforcement (SET will at least throw an error if I get the cardinality wrong).
July 5, 2018 at 7:31 am
Yeah, I've seen that in the wild quite a bit. This is one reason why primary keys should durable. If you're changing the value on a PK, you've likely made a bad design decision on both theoretical and practical grounds.
I get why it happens. There are a lot of columns that seem like a natural fit to be a PK at first glance and the need to change the values only creeps in later.
July 6, 2018 at 2:15 am
morlindk - Tuesday, July 3, 2018 3:00 AMSo simple. Amazing 32% can get it wrong!
Not that amazing when you consider that the behaviour is different when assigning column values in a Select, which is something more people are likely to have actually done in practice!
May 26, 2023 at 6:43 am
This was removed by the editor as SPAM
May 26, 2023 at 6:44 am
This was removed by the editor as SPAM
May 26, 2023 at 12:50 pm
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply