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 May 8, 2000

Update Methods Used in MS SQL Server 7.0

By Alexander Chigrik


Introduction
Direct update
Deferred update
Literature

Introduction

There are two update modes in MS SQL 7.0:

    - Direct update
    - Deferred update

In comparison with MS SQL 6.5, MS SQL 7.0 uses In-place update method with Direct update modes more frequently. It because Nullable fields with fixed length are kept with their full length.

Look at my previous article: http://www.databasejournal.com/features/mssql/article.php/1442311

There is no SET SHOWPLAN statement in MS SQL 7.0. There are SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, but this statements turns on SET NOEXEC ON, and you can see the showplan only separately with the query execution.

There is no system table syslogs in MS SQL 7.0. So this SQL statement:

SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs

will return error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syslogs'.

You may use the following undocumented command to view the log:

DBCC log (dbid, [, type={0|1|2|3|4}])

where dbid - is the database id, you can find this id in the sysdatabases
             system table in the master database.
      type - is the type of the output:

      0 - minimum information (operation, context, transaction id)

      1 - more information (plus flags, tags, row length)

      2 - very detailed information (plus object name, index name,
          page id, slot id)

      3 - full information about each operation

      4 - full information about each operation plus hexadecimal dump
          of the current transactional log's row.

by default type = 0


Direct update

You must not change the fields from the clustered index for Direct update. It is the most effective method of modification. When it is used the data changed on its physical place and only three rows will be written into the transactional log.

Look at this (I want to illustrate this on the real example):

USE pubs
GO
UPDATE discounts SET discount = 11.50
  WHERE discounttype = 'Initial Customer'
GO
DECLARE @dbid int
SELECT @dbid = dbid FROM master..sysdatabases WHERE name = 'pubs'
DBCC log (@dbid)
GO

These are the results from my computer:

Current LSN            Operation          Context            Transaction ID
---------------------- ------------------ ------------------ --------------
...

0000000e:000001e9:0001 LOP_BEGIN_XACT     LCX_NULL           0000:000006dc
0000000e:000001e9:0002 LOP_MODIFY_ROW     LCX_HEAP           0000:000006dc
0000000e:000001e9:0003 LOP_COMMIT_XACT    LCX_NULL           0000:000006dc

This is the example from my previous article (In-place update method is used instead On-page delete/insert method in this case):

USE pubs
GO
UPDATE jobs SET job_desc = 'Updated row' WHERE job_id = 1
GO
DECLARE @dbid int
SELECT @dbid = dbid FROM master..sysdatabases WHERE name = 'pubs'
DBCC log (@dbid)
GO

This is the results set:

Current LSN            Operation          Context            Transaction ID
---------------------- ------------------ ------------------ --------------
...

0000000e:000001ef:0001 LOP_BEGIN_XACT     LCX_NULL           0000:000006e2
0000000e:000001ef:0002 LOP_MODIFY_ROW     LCX_CLUSTERED      0000:000006e2
0000000e:000001ef:0003 LOP_COMMIT_XACT    LCX_NULL           
0000:000006e2


Deferred update

Deferred update mode is used when the data integrity could be corrupted if use direct update. For MS SQL 7.0 this update mode is used when you change the fields from the clustered index.

This is the example:

CREATE TABLE tbTest (id int primary key, test varchar(20))
GO
INSERT INTO tbTest VALUES (1, 'Test string')
GO
UPDATE tbTest SET id = id + 1
GO
DECLARE @dbid int
SELECT @dbid = dbid FROM master..sysdatabases WHERE name = 'pubs'
DBCC log (@dbid)
GO

These are the results from my computer:

Current LSN            Operation          Context            Transaction ID
---------------------- ------------------ ------------------ --------------
...

0000000f:0000017b:0001 LOP_BEGIN_XACT     LCX_NULL           0000:00000755
0000000f:0000017b:0002 LOP_DELETE_ROWS    LCX_MARK_AS_GHOST  0000:00000755
0000000f:0000017b:0003 LOP_SET_FREE_SPACE LCX_PFS            0000:00000755
0000000f:0000017b:0004 LOP_INSERT_ROWS    LCX_CLUSTERED      0000:00000755
0000000f:0000017b:0005 LOP_DELTA_SYSIND   LCX_CLUSTERED      0000:00000755
0000000f:0000017b:0006 LOP_COMMIT_XACT    LCX_NULL           
0000:00000755


Literature

1. SQL Server Books Online.

2. INF: Frequently Asked Questions - Server
http://support.microsoft.com/support/kb/articles/Q195/7/60.ASP?LN=EN-US&SD=SO&FR=0

3. "How can I view the SQL Server log?"
http://www.ntfaq.com/ntfaq/sql38.html


» 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