Incremental additions

Question

Post reply

Incremental additions

Praveen Goud Kotha

Ten Centuries

Points: 1141
More actions
October 28, 2010 at 8:28 pm

#388120

Comments posted to this topic are about the item Incremental additions
[font="Comic Sans MS"]Praveen Goud[/font]

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

Hardy21

SSCrazy Eights

Points: 9708

October 28, 2010 at 10:10 pm

#1243977

Nice question. Thanks

Thanks

srinibl SSC-Addicted Points: 458 More actions · Answer 2

srinibl

SSC-Addicted

Points: 458

October 28, 2010 at 11:14 pm

#1243983

Good question.

Thanks

Nakul Vachhrajani SSChampion Points: 10279 More actions · Answer 3

Great question. Thank-you.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 4

Good question, but I've liked a little more explanation and if possible, a reference.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Iulian -207023 SSCertifiable Points: 7510 More actions · Answer 5

Great question, thanks

Here is a reference to the compound operators: http://msdn.microsoft.com/en-us/library/cc645922.aspx

Regards,

Iulian

Nakul Vachhrajani SSChampion Points: 10279 More actions · Answer 6

I guess the subtle point is that SQL Server will NOT throw any syntax errors because ultimately what the user is making is a logical error.

It's one of those things that one would miss when writing code in a hurry, and then spend endless nights trying to figure out what went wrong.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 7

Iulian -207023 (10/29/2010)
Great question, thanks
Here is a reference to the compound operators: http://msdn.microsoft.com/en-us/library/cc645922.aspx
Regards,
Iulian

Thanks for the reference, but it still doesn't explain the question. In the question =+ is used, while the compound operator used in SQL Server is +=. I've done a little research, and it seems that a++ is the post-increment operator and ++a the pre-increment operator.

(see http://bytes.com/topic/c/answers/506780-difference-between-pre-increment-post-increment).

So it seems that the explanation of the question is wrong, as it states that the pre-increment operator is not used in SQL Server. SQL Server uses however a compound operator, meaning addition and assign, not incrementing the value. It still remains unknown to me why SQL Server ignores the + in =+ instead of giving a syntax error.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Carlo Romagnano SSC-Insane Points: 22282 More actions · Answer 8

So it seems that the explanation of the question is wrong, as it states that the pre-increment operator is not used in SQL Server. SQL Server uses however a compound operator, meaning addition and assign, not incrementing the value. It still remains unknown to me why SQL Server ignores the + in =+ instead of giving a syntax error.

Yes, explanation is wrong!

@a=+@b means simply that you assign an +(expression) to @a.

+ is unary operator: http://msdn.microsoft.com/en-us/library/aa276846(SQL.80).aspx

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 9

Carlo Romagnano (10/29/2010)
So it seems that the explanation of the question is wrong, as it states that the pre-increment operator is not used in SQL Server. SQL Server uses however a compound operator, meaning addition and assign, not incrementing the value. It still remains unknown to me why SQL Server ignores the + in =+ instead of giving a syntax error.
Yes, explanation is wrong!
@a=+@b means simply that you assign an +(expression) to @a.
+ is unary operator: http://msdn.microsoft.com/en-us/library/aa276846(SQL.80).aspx

Thanks for the link. Hence, @a =+ @b-2 means @b-2 value assign to @a so @a = -21.

Thanks

vk-kirov SSCertifiable Points: 7686 More actions · Answer 10

If the author wanted to trick us with the C-like pre-increment operator, this script would be more funny: 🙂

...

select @a = ++@b

select @a A

...

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 11

Hardy21 (10/29/2010)
Thanks for the link. Hence, @a =+ @b-2 means @b-2 value assign to @a so @a = -21.

Indeed. But why doesn't SQL Server give a syntax error? I would've liked that in the explanation.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hugo Kornelis SSC Guru Points: 64748 More actions · Answer 12

da-zero (10/29/2010)
Hardy21 (10/29/2010)
Thanks for the link. Hence, @a =+ @b-2 means @b-2 value assign to @a so @a = -21.
Indeed. But why doesn't SQL Server give a syntax error? I would've liked that in the explanation.

Becuase there is no invalid syntax. Spaces are (mostly) ignored by SQL Server, so @a =+ @b-2 is equivalent to @a=+@b, or to @a = +@b. The latter alternative is the most human-comprehensible. @a is assigned the result of applying the unqry + operator on @B. (And since the unary + operator is basically a no-op, you simply assign @a the value of @b-2).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 13

Hugo Kornelis (10/29/2010)
da-zero (10/29/2010)
Hardy21 (10/29/2010)
Thanks for the link. Hence, @a =+ @b-2 means @b-2 value assign to @a so @a = -21.
Indeed. But why doesn't SQL Server give a syntax error? I would've liked that in the explanation.
Becuase there is no invalid syntax. Spaces are (mostly) ignored by SQL Server, so @a =+ @b-2 is equivalent to @a=+@b, or to @a = +@b. The latter alternative is the most human-comprehensible. @a is assigned the result of applying the unqry + operator on @B. (And since the unary + operator is basically a no-op, you simply assign @a the value of @b-2).

Great. Thanks for the explanation! Now it seems really simple. 🙂

(I was waiting until you would post on this thread and take all doubt away :-D)

edit: your explanation comfirms my initial statement that the explanation of the questions' poster is incorrect, as it has nothing to do with pre-increment operators, but with misplacing the + so that SQL Server ignores the unary operator. Maybe the question would have been more difficult (or tricky) if it contained =-

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hugo Kornelis SSC Guru Points: 64748 More actions · Answer 14

da-zero (10/29/2010)
edit: your explanation comfirms my initial statement that the explanation of the questions' poster is incorrect, as it has nothing to do with pre-increment operators, but with misplacing the + so that SQL Server ignores the unary operator. Maybe the question would have been more difficult (or tricky) if it contained =-

Agreed; the explanation is lacking.

Pre-increment operators in C++ are a double + (or a double -) before the operand. Like "a = ++b" - this adds 1 to b, then assigns the result to a. You can also use post-increment: "a = b++" first assigns the value of b to a, then adds one to the value of b. And if you use -- instead of ++, you get pre- and post-decrement.

SQL Server understands neither of these. The post-increment syntax should result in a syntax error. The pre-increment syntax runs, but is interpreted as a sequence of two unary plus operators (so "SET @a = ++@b;" is interpreted as "SET @a = +(+(@b));"). With decrement, the results are reversed - post-decrement runs (because "SET @a = @b--;" is interpreted as "SET @a = @b-2", since -- is the start of an inline comment). The pre-decrement does not run (also becuase -- is a comment token).

Based on the supplied answer options and the explanation, I think the question author tried to confuse us with the syntax for compound operators, that was introduced in SQL Server 2008. "SET @a += @b-2;" (note that the order of = and + is not the same as in the QotD!) will generate an error message in SQL Server 2005 and older; in SQL Server 2008 and later, it will add the value of @b-2 to @a, i.e., it is equivalent to "SET @a = @a + @b-2;". One of the answers provided matches exactly what would be returned if the query had used += instead of =+.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/