Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 12, 2000

Update Methods Used in MS SQL 6.5

By Alexander Chigrik


Introduction
Direct update
  • In-place update
  • On-page delete/insert
  • Full delete/insert
  • Deferred update
    Literature

    Introduction

    There are two update modes in SQL Server 6.5:
    
      - Direct update
      - Deferred update
    
    Direct update contains three methods of modification:
    
      - In-place update
      - On-page delete/insert
      - Full delete/insert
    
    Deferred update mode always uses Full delete/insert method of modification.
    
    To see the update modes you can use SET SHOWPLAN ON statement. To see what
    commands were written into the transaction log, you can use the following
    statement:
    
    
      SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
    
    WHERE op - is the transaction log operation. There are some op values: op = 0 - is "BEGIN TRANSACTION" op = 4 - is "Insert Row" op = 5 - is "Delete Row" op = 6 - is "Deferred Update step 2 insert record" op = 9 - is "Modify Row" op = 11 - is "Deferred Update step 1 insert record" op = 12 - is "Deferred Update step 1 delete record" op = 30 - is "COMMIT TRANSACTION" I use the following trace flags: 3604 and 323 for getting more detail description of update methods. Trace flag 3604 sends trace output to the client. This trace flag is used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF. Trace flag 323 is undocumented trace flag. You can use it, if you want to see detail description of update methods.

    Direct update

    
    More effective then Deffered update. Rules for a Direct update:
    
      - The UPDATE cannot affect the column(s) that participates in a clustered 
    index.
    
    For multirow updates:
    
      - The UPDATE cannot affect the nullable column(s).
      - The UPDATE cannot affect the column(s) with variable length.
      - The table cannot include a column with the timestamp datatype.
      - The updated column cannot participate in a unique nonclustered index.
      - The updated column cannot participate in a nonunique nonclustered index,
        if the index used to find rows contains updated column.
    
    When it is used, rows are placed into the transaction log and then on the 
    data pages.
    
    

    In-place update

    
    Is most effective method of modification. When it is used the data changed
    on its physical place, and only one row will be written into the transaction
    log with "MODIFY" state.
    This is from SQL Server Books Online (Rules for an Update-In-Place):
    
      - The UPDATE cannot affect the column(s) that participates in a clustered 
    index.
      - The table cannot have an UPDATE trigger.
      - The table cannot be marked for replication.
    
    For single-row updates:
      - The updated column(s) can be variable length, but the new total row size 
    must fit on the same page as the old row.
      - The updated column(s) can participate in a nonunique nonclustered index 
    only if the index key is a fixed-width column.
      - The updated column(s) can participate in a unique nonclustered index 
    only if the index key is fixed-width and the WHERE
        clause criteria must have an exact match (based on an unique index 
    match_the index could be the same as the updated
        column).
      - The new row cannot include differing bytes by more than 50 percent of 
    the original row size, and the total number of
        discontiguous differing bytes is not more than 24.
    
    For multirow updates:
      - The updated column must be fixed length.
      - The updated column cannot participate in a unique nonclustered index.
      - The updated column can participate in a nonunique nonclustered index 
    only if the column is a fixed-width column (the index
        used to find rows cannot be the same as the updated column).
      - The table cannot include a column with the timestamp datatype.
    
    This is the example:
    
    
    USE pubs
    GO
    DBCC TRACEON (3604)
    GO
    DBCC TRACEON (323)
    GO
    SET SHOWPLAN ON
    GO
    UPDATE stores SET state = 'UT' WHERE stor_id = '6380'
    GO
    SET SHOWPLAN OFF
    GO
    SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
    GO
    
    This is the results:
    STEP 1
    The type of query is UPDATE
    The update mode is direct
    FROM TABLE
    stores
    Nested iteration
    Using Clustered Index
    TO TABLE
    stores
    Update: in-place, clust, safeind[0]=0x1
    
    (1 row(s) affected)
    
    STEP 1
    The type of query is SETOFF
    TRAN_ID        LOG_RECORD
    -------------- ----------
    ...
    
    0x380300000000 0
    0x380300000000 9
    0x380300000000 30
    
    You can see that in this case "The type of query is UPDATE", "The update mode is direct" and In-place method of modification is used. Look at the last three rows from the transaction log, so when used the In-place update method and only one record is updated then only three rows will be placed into the transaction log: - BEGIN TRANSACTION - Modify Row - COMMIT TRANSACTION

    On-page delete/insert

    
    This method of modification is used when could be used In-place update,
    but one or more of the following conditions are present:
    
      - There is update trigger on the updated table.
      - The updated table participate in the replication process.
      - The size of the record was changed.
    
    This is the example:
    
    
    USE pubs
    GO
    DBCC TRACEON (3604)
    GO
    DBCC TRACEON (323)
    GO
    SET SHOWPLAN ON
    GO
    UPDATE jobs SET job_desc = 'Updated row' WHERE job_id = 1
    GO
    SET SHOWPLAN OFF
    GO
    SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
    GO
    
    This is the results:
    STEP 1
    The type of query is UPDATE
    The update mode is direct
    FROM TABLE
    jobs
    Nested iteration
    Using Clustered Index
    TO TABLE
    jobs
    Update: on-page delete/insert, clust, safeind[0]=0x1
    
    (1 row(s) affected)
    
    STEP 1
    The type of query is SETOFF
    TRAN_ID        LOG_RECORD
    -------------- ----------
    ...
    
    0x3b0300001800 0
    0x3b0300001800 5
    0x3b0300001800 4
    0x3b0300001800 30
    
    You can see that in this case "The type of query is UPDATE", "The update mode is direct" and On-page delete/insert method of modification is used. Look at the last four rows from the transaction log, so when used the On-page delete/insert method and only one record is updated then only four rows will be placed into the transaction log: - BEGIN TRANSACTION - Delete Row - Insert Row - COMMIT TRANSACTION

    Full delete/insert

    
    This method of modification is used with Direct update mode when
    there are no space for new inserted row on the updated page.
    SQL Server 6.5 creates new row on the new page in this case. This
    method of modification is always used with Deferred update mode.
    
    

    Deferred update

    
    When it is used, the rows are placed into the transaction log with
    no-op states ("Deferred Update step 1 delete record" and "Deferred
    Update step 1 insert record" states), then SQL Server returns to the
    beginning of the transaction and starts applying the delete operations,
    after finishes the deletes operations, it applies the inserts operations
    and only after that rows are placed on a data page.
    
    Rules for a Deferred update:
    
      - The UPDATE affect the column(s) that participates in a clustered index.
    
    For multirow updates:
    
      - The UPDATE affect the nullable column(s).
      - The UPDATE affect the column(s) with variable length.
      - The table includes a column with the timestamp datatype.
      - The updated column(s) participates in a unique nonclustered index.
      - The updated column(s) participates in a nonunique nonclustered index,
        if the index used to find rows contains updated column(s).
    
    This is the example:
    
    
    USE pubs
    GO
    DBCC TRACEON (3604)
    GO
    DBCC TRACEON (323)
    GO
    SET SHOWPLAN ON
    GO
    UPDATE discounts SET lowqty = 100
    GO
    SET SHOWPLAN OFF
    GO
    SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
    GO
    
    This is the results:
    STEP 1
    The type of query is UPDATE
    The update mode is deferred
    FROM TABLE
    discounts
    Nested iteration
    Table Scan
    TO TABLE
    discounts
    Update: full delete/insert, deferred mode, no clust, safeind[0]=0xfe
    Update: full delete/insert, deferred mode, no clust, safeind[0]=0xfe
    Update: full delete/insert, deferred mode, no clust, safeind[0]=0xfe
    
    (3 row(s) affected)
    
    STEP 1
    The type of query is SETOFF
    TRAN_ID        LOG_RECORD
    -------------- ----------
    ...
    
    0x140300000f00 0
    0x140300000f00 12
    0x140300000f00 11
    0x140300000f00 12
    0x140300000f00 11
    0x140300000f00 12
    0x140300000f00 11
    0x140300000f00 5
    0x140300000f00 5
    0x140300000f00 5
    0x140300000f00 6
    0x140300000f00 6
    0x140300000f00 6
    0x140300000f00 30
    
    You can see that in this case "The type of query is UPDATE", "The update mode is deferred" and full delete/insert method of modification is used. Look at the last 14 rows from the transaction log, so when used deferred update mode this rows will be placed into the transaction log: - BEGIN TRANSACTION - Deferred Update step 1 delete record - Deferred Update step 1 insert record - Deferred Update step 1 delete record - Deferred Update step 1 insert record - Deferred Update step 1 delete record - Deferred Update step 1 insert record - Delete Row - Delete Row - Delete Row - Deferred Update step 2 insert record - Deferred Update step 2 insert record - Deferred Update step 2 insert record - COMMIT TRANSACTION

    Literature

    
    1. SQL Server Books Online.
    
    2. "Update Methods Used in SQL Server 6.0"
       http://support.microsoft.com/support/kb/articles/Q135/8/71.asp?LN=EN-US&SD=SO&FR=0
    
    3. "Viewing Transaction Log"
       http://www.swynk.com/friends/achigrik/ViewLog.asp
    
    


    » See All Articles by Columnist Alexander Chigrik




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM