Poking about with DBCC PAGE (Part 1 of ?)

re: Poking about with DBCC PAGE (Part 1 of ?)

kirchner — Tue, 15 Aug 2006 19:45:44 GMT

Paul

Can you clarify a thing? Two things actually.

You said:
"this is different and simpler than SQL Server 2000 which had one bit per nullable column only"

I find that SQL 2000 will store the null bitmap even though all columns are not null. And acording to Kalen Delaney, in her internals book, it's this way, null bitmap is always stored.

I created a table with 9 tinyint not null columns, and inserted:
insert example2 values (1, 2, 3, 4, 5, 6, 7, 8, 9)

DBCC PAGE(tempdb, 1, 4051, 1):

Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
1EBBA060: 000d0010 04030201 08070605 00000909 ................
1EBBA070: 00

I believe the first 00 in 00000909, and the 00 in the next line are the null bitmap, for 9 columns, 2 bytes. And no variable length columns here.

Is this right?

The second question is based on the analys to the first one, it appears that SQL 2000 will show each four bytes reversed.
Is DBCC PAGE showing the bytes in a different order on SQL 2000 and SQL 2005, or is the physical record stored different in these versions?

Thnaks,

Marcos

re: Poking about with DBCC PAGE (Part 1 of ?)

Paul Randal - MSFT — Thu, 31 Aug 2006 19:09:05 GMT

Yup - my mistake - there's always a bit per column in both versions.

re: Poking about with DBCC PAGE (Part 1 of ?)

Paul Randal - MSFT — Fri, 01 Sep 2006 21:24:23 GMT

btw, yes - I changed the ordering the DBCC PAGE dumps the bytes between SQL Server 2000 and 2005 - its was doing it backwards in 2000.

SQL Server Storage Engine Poking about with DBCC PAGE Part 1 of | storage bench

SQL Server Storage Engine Poking about with DBCC PAGE Part 1 of | storage bench — Sun, 14 Jun 2009 13:03:07 GMT

PingBack from http://thestoragebench.info/story.php?id=5447

SQL Server Storage Engine Poking about with DBCC PAGE Part 1 of | fix my credit

SQL Server Storage Engine Poking about with DBCC PAGE Part 1 of | fix my credit — Wed, 17 Jun 2009 04:47:52 GMT

PingBack from http://fixmycrediteasily.info/story.php?id=9865

SQL Server Storage Engine Poking about with DBCC PAGE Part 1 of | pool toys

SQL Server Storage Engine Poking about with DBCC PAGE Part 1 of | pool toys — Thu, 18 Jun 2009 11:57:00 GMT

PingBack from http://pooltoysite.info/story.php?id=9148