Post reply

Some doubts on T-SQL basic fundamentals

  • December 17, 2012 at 12:57 am

    #268342

    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?

  • December 17, 2012 at 1:01 am

    #1569031

    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

  • December 17, 2012 at 1:37 am

    #1569037

    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?

  • December 17, 2012 at 1:51 am

    #1569041

    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

  • December 17, 2012 at 2:36 am

    #1569053

    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.

  • December 17, 2012 at 2:46 am

    #1569059

    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