Some doubts on T-SQL basic fundamentals

  • If there is an update statement using 'where' clause, and the 'where' clause returns ten rows, how exactly sql engine will process this?

    1. Will this store the read result(10 rows) in temp table, and update the rows one by one. Or, can issue update statement for more than one rows at a time? Or will issue the update stmt in sequential manner - row 9 update is not completed yet and row 10 update command issued?

  • Could you be more specific please?

    If you mean something like this:

    UPDATE Table SET SomeColumn = SomeValue WHERE SomeID between 1 and 10 -- updates 10 rows

    then there's no temp table involved there. SQL locates the 10 rows (via seek or scan as applicable) and updates each one.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gila for making it specific. Yes, that is the required scenario. But my concern remain unanswered is - how exactly the update process will occur internally? All in one shot? Or in a sequntial manner -- If yes, which row first?

  • ganeshkumar005 (12/17/2012)


    But my concern remain unanswered is - how exactly the update process will occur internally? All in one shot? Or in a sequntial manner -- If yes, which row first?

    Why does it matter?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I am facing a deadlock issue. I can see application is sending update command on db and before that SPID is completed, it is sending the same command again, may be for a different row, because page ID is different in deadlock graph.

  • Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    DBCC TRACEON(1222,-1)

    How the query processor internally executes an update will have no bearing on your deadlock. All required rows are locked exclusive before the update starts and those locks are only released after the transaction commits

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

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