Stairway to T-SQL: Beyond The Basics Level 8: Coding Shortcuts using += and -= Operators

Question

Post reply

Stairway to T-SQL: Beyond The Basics Level 8: Coding Shortcuts using += and -= Operators

Greg Larsen

SSC-Insane

Points: 20966
More actions
May 29, 2014 at 6:36 pm

#308573

Comments posted to this topic are about the item Stairway to T-SQL: Beyond The Basics Level 8: Coding Shortcuts using += and -= Operators
Gregory A. Larsen, MVP

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

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

klaus.kuehne SSC Enthusiast Points: 164 More actions · Answer 1

Hello,

it's worth to be mentioned that ++ and -- operators don't exist in TSQL. The following example code:

declare @ int = 1, @j-2 int, @k int

Set @j-2 = ++@i

Set @k = @i++

Print @i, @j-2, @k

will be executed without an error, but @j-2 and @k get content "1".

It seems that "++@i" evaluates to "+(+(@i))" ==> "0+0+@i" ==> 1 and "@i++" to "@i+0+0" ==> 1.

Regards,

Klaus Kuehne

Robert.Sterbal SSCrazy Points: 2855 More actions · Answer 2

The -- operator would cause quite an issue, since that is the comment mark

klaus.kuehne SSC Enthusiast Points: 164 More actions · Answer 3

Indeed. "set @j-2 = @i--" is also a nice joke. Good idea for placing some T-SQL bombs in our companies 🙂

m2c2 SSC Enthusiast Points: 116 More actions · Answer 4

I've been at this for a bit now, and I don't see the need for the "=" part of these operations. It would seem that "+" and "-" perform the same functions by themselves.

Maybe I'm overlooking something basic? My wife always asks why I can't see things in our fridge that turn up in clear view...after she points them out. :blink:

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

CELKO (7/9/2014)
These shorthands are a bad idea.

I'm gonna agree with that. I don't like seeing shorthand in the code.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ed Wagner SSC Guru Points: 287024 More actions · Answer 6

SQLRNNR (7/9/2014)
CELKO (7/9/2014)
These shorthands are a bad idea.
I'm gonna agree with that. I don't like seeing shorthand in the code.

I'm with you - I don't like it. I have to admit that until I read that article, I've never even seen them before. I agree with the premise that nobody wants to type more than they have to, but I believe whole-heartedly that readability and maintainability are vastly more important than typing a few extra characters.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Robert.Sterbal SSCrazy Points: 2855 More actions · Answer 7

we could use a like button on this forum... on all the posts that are saying this is an unnecessary extension to t-sql

Alan Burstein SSC Guru Points: 61136 More actions · Answer 8

It's worth noting that these are referred to as Compound Assignment Operators and you can use them for multiplication (*=), division ( /=), modulo (%=), and a few bitwise operations (note the link above).

Its interesting to see the comments - not a a lot of love here apparently for them but I do like them personally ( style choice perhaps).

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

asutorius SSC Enthusiast Points: 138 More actions · Answer 9

I was curious how these two concatenate examples compared in speed, I placed the examples in their own tabs in my local Sql Server 2014 and turned on Include Client Side Statistics for each example, then ran them.

The non-shorthand example beat the shorthand in Client Execution Time, Bytes received from server, Total Execution time, and Wait time on server replies.

From that I'm inferring that just because you type less characters, doesn't mean it's the best solution.

Now I'm wondering if I'm seeing the stats correctly, do y'all get those results too?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

asutorius (7/15/2016)
I was curious how these two concatenate examples compared in speed, I placed the examples in their own tabs in my local Sql Server 2014 and turned on Include Client Side Statistics for each example, then ran them.
The non-shorthand example beat the shorthand in Client Execution Time, Bytes received from server, Total Execution time, and Wait time on server replies.
From that I'm inferring that just because you type less characters, doesn't mean it's the best solution.
Now I'm wondering if I'm seeing the stats correctly, do y'all get those results too?

Can you post your test harness so that we can duplicate your tests?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

babap SSC Rookie Points: 35 More actions · Answer 11

BEWARE...these short cuts are NOT either ISO SQL standard or ANSI SQL.

This is going back in time BEFORE the ISO SQL standard was created.

Shortcuts create long term pains!!!

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 12

babap (7/16/2016)
BEWARE...these short cuts are NOT either ISO SQL standard or ANSI SQL.
This is going back in time BEFORE the ISO SQL standard was created.
Shortcuts create long term pains!!!

Keep in mind that almost anything you do with variables means that your code will be neither ANSI or ISO compliant. While I personally dislike such shortcuts and "front ender" code such as != (I prefer <>), if your goal is to write only ANSI or ISO compliant code for purposes of supposed easy migration, then you're missing out on a huge amount of power and performance offered by the non ANSI/ISO extensions that every RDBMS engine has built into it. It's like not being able to use a scientific calculator because someone in the world might have only a 4 function calculator to work with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Celko SSCertifiable Points: 5749 More actions · Answer 13

I attended an IBM briefing on analytic functions in version 11 of DB2 last week. Part of the presentation consisted of showing TBC analytic benchmarks, which are some pretty complicated and ugly things. There was a sweet a suite of 99 programs which all of the products have been run against. What was interesting to me was how the TPC Council ranks the products and the queries. Imagine a grid with the identifier of each query, colored green (runs immediately as written), red (cannot be run at all), and two shades of orange (could be rewritten in dialect with minor effort, or could be rewritten in dialect with major effort).

Not too surprisingly, since it is a vendor presentation, the new version of DB2 was all green. But in fairness, DB2 has really, really good ANSI/ISO conformance. Some of the other products like SQL Server, Oracle, etc. had red cells in the grid and various amounts of orange.

Now I assume the queries were provided by each of these various product vendors and submitted to the TPC. What it showed was that you can write ANSI/ISO standard SQL in all the major SQL products (and some of the minor ones), either directly or with some minor effort.

What was interesting was that the queries written to standard (the green guys) had better performance numbers than the orange. Of course the red guys had no performance at all, but that is another issue.

The conclusion I came to was to was (1) you really can write ANSI/ISO standard SQL in virtually every product on the market with little or no effort (2) avoiding highly proprietary features actually has a pay off when you start doing large amounts of data. Vendors tend to spend their efforts on improving core standard features, and not adding bells and whistles to proprietary features. (3) since one vendor shops no longer exist in the real world (it is not like the days when we had IBM and the BUNCH), portable simple code makes moving data around your your company much, much easier.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 14

CELKO (7/17/2016)
I attended an IBM briefing on analytic functions in version 11 of DB2 last week. Part of the presentation consisted of showing TBC analytic benchmarks, which are some pretty complicated and ugly things. There was a sweet a suite of 99 programs which all of the products have been run against. What was interesting to me was how the TPC Council ranks the products and the queries. Imagine a grid with the identifier of each query, colored green (runs immediately as written), red (cannot be run at all), and two shades of orange (could be rewritten in dialect with minor effort, or could be rewritten in dialect with major effort).
Not too surprisingly, since it is a vendor presentation, the new version of DB2 was all green. But in fairness, DB2 has really, really good ANSI/ISO conformance. Some of the other products like SQL Server, Oracle, etc. had red cells in the grid and various amounts of orange.
Now I assume the queries were provided by each of these various product vendors and submitted to the TPC. What it showed was that you can write ANSI/ISO standard SQL in all the major SQL products (and some of the minor ones), either directly or with some minor effort.
What was interesting was that the queries written to standard (the green guys) had better performance numbers than the orange. Of course the red guys had no performance at all, but that is another issue.
The conclusion I came to was to was (1) you really can write ANSI/ISO standard SQL in virtually every product on the market with little or no effort (2) avoiding highly proprietary features actually has a pay off when you start doing large amounts of data. Vendors tend to spend their efforts on improving core standard features, and not adding bells and whistles to proprietary features. (3) since one vendor shops no longer exist in the real world (it is not like the days when we had IBM and the BUNCH), portable simple code makes moving data around your your company much, much easier.

You do have to remember that the conference and the test were sponsored by the makers of DB2. 😉

Shifting gears a bit, what is the ANSI and/or ISO standard for stored procedures... especially for how parameters will be passed?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)