July 25, 2013 at 3:57 am
Alan.B (7/24/2013)
Erland Sommarskog (7/24/2013)
Alan.B (7/24/2013)
Why is the SELECT@x=@x+ method not guaranteed to work?
Why would it?
See this KB article Pay particular attention to the first sentence under Cause.
I say it would work based on the example I posted (which works). It produces the exact same plan and answer (except for the leading comma) as what Chris Posted which I believe is guaranteed to work. I need to read the article a little more (as well as this one[/url]) but I think it should work just fine.
...
As I have pointed out in my previous post, one of the reasons why this concatenation may not work properly is a query parallelising. You cannot guarantee that the query plan stays the same for ever (until you inforce it with hints).
And there is another one: I have seen it happened! This style query did work fine for couple of years, then occasionally started to return unexepected results. It was not easy to find what was going wrong, due to the problem was not easy to reproduce.
And again. If query of this styler is very basic, then probability of this problem is very low. As soon as such query is complecated by JOIN's or GROUP BY - probabilty of this query to return unexpected results grows.
In SQL2008 MS introduces compound operators into T-SQL eg +=, -=, *= etc. Check BoL, even for them, the only sample of code MS added into BoL is a setting values using SELECT or SET - not SELECT from Table as they are not replacement for aggregate functions.
Why not to sum values just by doing SELECT @v-2 += Column FROM table?
The reason is exactly the same. It's not guaranteed that it will aggregate all values!
July 25, 2013 at 12:30 pm
Eugene Elutin (7/25/2013)
Alan.B (7/24/2013)
Erland Sommarskog (7/24/2013)
Alan.B (7/24/2013)
Why is the SELECT@x=@x+ method not guaranteed to work?
Why would it?
See this KB article Pay particular attention to the first sentence under Cause.
I say it would work based on the example I posted (which works). It produces the exact same plan and answer (except for the leading comma) as what Chris Posted which I believe is guaranteed to work. I need to read the article a little more (as well as this one[/url]) but I think it should work just fine.
...
As I have pointed out in my previous post, one of the reasons why this concatenation may not work properly is a query parallelising. You cannot guarantee that the query plan stays the same for ever (until you inforce it with hints).
And there is another one: I have seen it happened! This style query did work fine for couple of years, then occasionally started to return unexepected results. It was not easy to find what was going wrong, due to the problem was not easy to reproduce.
And again. If query of this styler is very basic, then probability of this problem is very low. As soon as such query is complecated by JOIN's or GROUP BY - probabilty of this query to return unexpected results grows.
In SQL2008 MS introduces compound operators into T-SQL eg +=, -=, *= etc. Check BoL, even for them, the only sample of code MS added into BoL is a setting values using SELECT or SET - not SELECT from Table as they are not replacement for aggregate functions.
Why not to sum values just by doing SELECT @v-2 += Column FROM table?
The reason is exactly the same. It's not guaranteed that it will aggregate all values!
I get it now. Thanks Eugene and Erland!
-- Itzik Ben-Gan 2001
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply