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

  • Greg Larsen

    SSC-Insane

    Points: 20610

    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

  • klaus.kuehne

    SSC Enthusiast

    Points: 164

    Hello,

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

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

    Set @j = ++@i

    Set @k = @i++

    Print @i, @j, @k

    will be executed without an error, but @j 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

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

  • klaus.kuehne

    SSC Enthusiast

    Points: 164

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

  • m2c2

    SSC Enthusiast

    Points: 116

    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: 281210

    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: 286958

    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.

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    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: 61037

    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).

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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

    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: 994663

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • babap

    SSC Rookie

    Points: 35

    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: 994663

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Joe Celko

    SSCertifiable

    Points: 5553

    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: 994663

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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