Post reply

InPlace Updates for Key columns

  • August 22, 2014 at 1:04 pm

    #292609

    Hello Experts,

    I Have a question. Updating key columns of a table doesn't performs In Place Updates but does delete and insert.

    Can some explain me what is the reason of not doing In Place Updates and going for Insert and delete, which creates overhead for transaction logs and also causes page splitting ?

  • August 22, 2014 at 1:39 pm

    #1740397

    i believe it's a design structure built into SQL, where in place updates are never truly performed.

    'The design makes sense, because it allows you to reference both old and new values, via the INSERTED and DELETED virtual tables, either in a trigger or via an output clause;

    it also allows a rollback to previous values, which keeps the transaction atomic /consistent. as both old and new values are in place for the transaction to process in case of an error.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 22, 2014 at 1:51 pm

    #1740401

    I am jut trying to understand the statement "key columns of a table doesn't performs In Place Updates but does delete and insert"

    Isn't every column of table available through the inserted and deleted virtual tables? I think they are available.

    What are key columns and what is this inplace update in this context?

    Thanks

  • August 23, 2014 at 2:27 am

    #1740450

    By key columns I mean key columns of clustered and non clustered indexes.

    Data columns can do in place updates.

    Answering to atomicity, even in place updates are atomic and rolled back.

    My question is that if in place updates for key column is design concept or some logic behind that

  • August 23, 2014 at 3:16 am

    #1740456

    Because a change to a key column (key of an index) means that the row must move from one page to another (indexes are logically ordered by their keys). If the update was done inplace, then the index would have a value in the wrong place.

    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

  • August 23, 2014 at 3:17 am

    #1740457

    Lowell (8/22/2014)

    i believe it's a design structure built into SQL, where in place updates are never truly performed.

    False.

    Inplace updates are indeed performed. Any update which is not a key column (key of a clustered or nonclustered index) is performed as an in-place update. Split updates (split into delete/insert) are the exception, not the norm.

    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

  • August 23, 2014 at 4:36 am

    #1740461

    Thanks Gail,

    Your knowledge in SQL is really admirable.

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

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