December 16, 2010 at 8:28 pm
Hi all, i did a test about DBCC IND and DBCC page .
Script:
--T-SQL
CREATE TABLE dbo.t2
(
ID int,
Col1 varchar(10),
Col2 varchar(20)
);
INSERT INTO dbo.t2 SELECT 1,REPLICATE('a',10),REPLICATE('b',10)
DECLARE @DBID int,@TableID int;
SELECT @DBID=DB_ID(),@TableID=OBJECT_ID('dbo.t2');
DBCC IND(@DBID,@TableID,-1)
--this shows :
1274127813071517020172057594039107584In-row data1
DBCC TRACEON (3604)
--DBCC PAGE (@DBID, 1,22,3)
--then dump the page info
DBCC PAGE(@DBID, 1,274,1)
shows:
DATA:
Slot 0, Offset 0x60, Length 51, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 51
Memory Dump @0x000000000F7EA060
0000000000000000: 70000800 01000000 03000002 001b0025 †p..............%
0000000000000010: 00616161 61616161 61616162 62626262 †.aaaaaaaaaabbbbb
0000000000000020: 62626262 62000000 00000000 00310000 †bbbbb........1..
0000000000000030: 00000†††††††††††††††††††††††††††††††...
OFFSET TABLE:
Row - Offset
0 (0x0) - 96 (0x60)
i try to explain:
byte one
0x70 01110000
BIT 0: SQL SERVER 2005/2008
BIT 1-3 :PRIMARY DATA
BIT 4 : HAS NULL
BIT 5:HAS VAR
BIT 6:NOT USED
BIT 7: NOT GHOST
Second Byte
0x00 : NOT USED
Thrid Byte
0x0008 :offset 8
the fix length int ,i got value 1
then next byte
0300 : 3 columns
00 : not null
0002 :2 columns
var column endoffset 0x001B (i got value aaa....)
var column end offset 0x0025 (i got value bbbb...)
but ,i don's know the meaning about last bytes....(marked as red in shown text)
please help me ,thks a lot
December 16, 2010 at 9:42 pm
i got a answer:
It's the 14-byte versioning tag [timestamep, pointer into tempdb].
but i never set allow_snapshot_isloation on or read_committed_snapshot on
December 16, 2010 at 10:44 pm
Each database row may use up to 14 bytes at the end of the row for row versioning information. The row versioning information contains the transaction sequence number of the transaction that committed the version and the pointer to the versioned row. These 14 bytes are added the first time the row is modified, or when a new row is inserted, under any of these conditions:
READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION options are ON.
The table has a trigger.
Multiple Active Results Sets (MARS) is being used.
Online index build operations are currently running on the table.
These 14 bytes are removed from the database row the first time the row is modified under all of these conditions:
READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options are OFF.
The trigger no longer exists on the table.
MARS is not being used.
Online index build operations are not currently running.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply