Update Methods Used in SQL Server 7.0
Introduction
There are two update modes in SQL Server 7.0:
- Direct update
- Deferred update
In comparison with SQL Server 6.5, SQL Server 7.0 uses In-place update method
with Direct update modes more frequently. It because Nullable field with fixed
length is kept with their full length.
There is no SET SHOWPLAN statement in SQL Server 7.0. There are
SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, but these statements
turn on SET NOEXEC ON, and you can see the showplan only separately
from the query execution.
There is no syslogs system table in SQL Server 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 should not modify the clustered index columns to use Direct update.
It is the most effective method of modification. When it is used, the rows
will be modified on it physical place and only three rows will be written
into the transaction log.
This is the 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
This is the result set:
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
In this example In-place update method is used instead of On-page
delete/insert method:
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 result 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 SQL Server 7.0 this update
mode is used when you modify the clustered index columns.
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
This is the result set:
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
