Post reply

Does SSMS execute SQL code for each row, one row at a time?

  • January 16, 2020 at 3:44 am

    #3715871

    I am starting with the data in the picture titled "Sales Table 1."  The Team column has some repeating values.  I am doing an UPDATE statement WHERE Team = 8, so there are 4 records that get updated.  Here is the code.

      UPDATE dbo.SalesRep
      SET Region = 'South'
      WHERE Team = 8;

    How does SSMS actually execute the UPDATE statement?  Does SSMS look at row 1, then executes the UPDATE statement?  Then go to row 2 and execute the UPDATE statement, then to row 3, etc. until SSMS has executed the UPDATE statement for each row?  Is this what is actually happening?

    Or is SSMS executing the UPDATE statement only once and somehow applying it to all rows all at once?

    Edit:  Jeff Moden.  Somehow I believe this ties in to the article you wrote on RBAR.

    Attachments:
    You must be logged in to view attached files.

  • January 16, 2020 at 2:09 pm

    #3715982

    Would you believe... both.

    If you look at an execution plan that is in row mode, not batch mode, it's actually doing all the commands it does, one row at a time. However, if you get batch mode execution, you can see a situation where it does groups of rows.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 1, 2020 at 12:16 am

    #3722185

    Grant Fritchey wrote:

    Would you believe... both.

    If you look at an execution plan that is in row mode, not batch mode, it's actually doing all the commands it does, one row at a time. However, if you get batch mode execution, you can see a situation where it does groups of rows.

    I haven't gotten to execution plans yet, but I will keep this in mind.  Thank you for replying.

Viewing 3 posts - 1 through 2 (of 2 total)

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