SQL Server resolves row-overflow data storage

The maximum data row size in SQL Server2000 is 8060 (we can use a size of 8039), that is, when the table is created, the sum of all columns cannot be more than 8060. In 2005, this limit remains for fixed-length data (although in 2005 we can use a size of 8053 instead of 8039). So is it possible for a row in SQL SERVER2005 to store only up to 8053 bytes of data? Can you break the 8060 limit?

There is no way in SQL SERVER2000, but it is possible in SQL SERVER2005. Using variable-length data in SQL SERVER2005 can break the 8060 limit. Because the restrictions on each row of data in the SQL SERVER2005 are adjusted, the length of each column must still be within 8000 of each row for tables containing variable-length types, but their merged widths can exceed the 8060B limit.

In SQL SERVER2005, you can store a variable-length column in a row overflow page. When a column needs to be transferred from a regular page to a row overflow page, SQL 2005 retains a pointer containing row overflow information as part of the original record, the size of the pointer is 24B, and for each variable-length column, the record requires 2 bytes, regardless of whether the column is stored in records.

Test

CREATE Table TB (col char (7000), col2 varchar (3000), col3 varchar (3000))

Go

INSERT INTO TB

VALUES (' AAA ', replicate (' BBB ', 1000), replicate (' CCC ', 1000))

Go

DBCC IND (TEST,TB,-1)-– The resulting page number is 89,80,6321,6315. Where 89,6321 is an IAM page, 80 and 6315 are data pages

DBCC TRACEON (3604)

DBCC page (test,1,89,1)

DBCC page (test,1,80,1)

DBCC page (test,1,6321,1)

DBCC page (test,1,6315,1)

The following resolves the generated IAM pages and data pages separately to see how row-overflow data is stored in SQL SERVER2005.

One, parse IAM page

Because 89 and 6321 page structure is the same, parse the first one can, to 89 pages for example.

DBCC TRACEON (3604)

DBCC page (test,1,89,1)

The results obtained:

1, the page total two lines

2. The first line records the data page of the IAM record (the following comment illustrates the effect of the data)

00000000:00005e00 00000000 00000000 00000000†--The length of the line

00000010:00000000 00000000 00000000 00000000† .........

00000020:00000000 00000000 00000000 01005000†--The responsible data page ID

00000030:00000100 00000000 00000000 00000000† .........

Second, parse data page

1, parsing 80 page data:

00000000:30005C1B 61616120 20202020 20202020–-before the first four bytes are not explained

......

00001b50:20202020 20202020 20202020 0300f802

-–0300 total three columns, F8 null bitmap, 0200 variable length column has two columns

00001b60:007d9b95 9b020000 65010000 00f65c00

-Although the second and third columns of data are stored on another data page, each column will still occupy two bytes.

00001b70:00b80b00 00ab1800 00010000 00020000

00001b80:65010000 00c04700 00b80b00 00ab1800

00001b90:00010001 00

020000 65010000 00f65c00 00b80b00 00ab1800 00010000 00

The first row overflows the pointer

020000 65010000 00c04700 00b80b00 00ab1800 00010001 00

The second row overflows the pointer

A pointer with a length of 24 bytes. The 24-byte contains the following sections, respectively:

0200	00	65	01000000	f65c0000	b80b0000	ab180000	0100	0000
overflow column type	at B- tree species level	temporarily unused, has no practical meaning	lob number of data updates	for The DBCC CHECKTABLE uses a random value that does not change in the cycle of lob existence	the length of the column. (calculated as 00000bb8 )	the page number where this part of the data is located	the file number of this part of the data	slot number on the page where the data resides

2, for row overflow pages, the page type used is LOB. For the way the page is recorded, it is described later.