Post reply

InPlace update for Included Columns

  • October 8, 2014 at 12:18 am

    #297194

    The Delete-Insert update happens for key columns of Indexes and that makes sense.

    But I am confused that why Delete-Insert update happens for Included columns of nonclustered indexes, where I expected them to be InPlace updates ???

  • October 8, 2014 at 2:10 am

    #1750852

    How are you seeing that it's a split update rather than an in-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

  • October 8, 2014 at 4:07 am

    #1750874

    here is the output of updating column suffix for Person.Person table in adventureworks2008 database

    I have modified the index to include Suffix column as shown below

    CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]

    (

    [LastName] ASC,

    [FirstName] ASC,

    [MiddleName] ASC

    )

    INCLUDE ( [Suffix]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    update Person.Person

    set Suffix = 'Jr.'

    where BusinessEntityID = 1

    Now , I am seeing the log

    select * From fn_dblog(null,null)

    000000c5:0000003f:0012 LOP_BEGIN_CKPT LCX_NULL 0000:00000000

    000000c5:00000047:0001 LOP_END_CKPT LCX_NULL 0000:00000000

    000000c5:00000048:0001 LOP_BEGIN_XACT LCX_NULL 0000:00004e98

    000000c5:00000048:0002 LOP_MODIFY_COLUMNS LCX_CLUSTERED 0000:00004e98

    000000c5:00000048:0003 LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:00004e98

    000000c5:00000048:0004 LOP_INSERT_ROWS LCX_INDEX_LEAF 0000:00004e98

    000000c5:00000048:0005 LOP_COMMIT_XACT LCX_NULL 0000:00004e98

    Hope this helps you

  • April 10, 2015 at 12:54 am

    #1790081

    Hello Experts,

    One unanswered question from my old post

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

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