Table and Column layout vs. Page allocation

Question

Table and Column layout vs. Page allocation

tombynum

Mr or Mrs. 500

Points: 597
More actions
December 5, 2003 at 9:31 am

#107390

I'm trying to ask a question about how SQL stores it's pages (so please bear with me if the calculations are wrong:)
When creating a table, does SQL server reorder the columns to optimize it's "page" layout and actual data storage?
Perhaps a more general and equally important question is...is page storage of a table equal to the sum of it's column widths?
(...this example has been greatly simplified so please be gentle...)
Here's an example: A simple table with 5 columns
Field1 char(4096)
Field2 char(3072)
Field3 char(2048)
Field4 char(3072)
Field5 char(4096)
BOL tells me that a "page" is 8 kbs - so in my calculations, the table above example represents 2 pages
Now, Field1 and Field2 don't fill a page and Field3 causes it to spill over to the next...the question is, even though I've built the table in this layout, will SQL store it as follows:
Field1 char(4096)
Field5 char(4096)
Field2 char(3072)
Field3 char(2048)
Field4 char(3072)
...to optimize the page layout and break points?
...just wondering...

The ~BEST~ solution is always the simplest one!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

As your column widths cannot sum to more than 8060 bytes, there's something wrong with your examples. A row's size cannot exceed one page (BLOBs, of course, being a special case stored elsewhere). If your columns are varchar rather than char, you will be allowed (with a warning) to exceed the 8060 byte maximum when creating the table, but not when inserting or updating any rows.

--Jonathan

ThomasH SSCertifiable Points: 7437 More actions · Answer 2

Way back, I remember being told that SQL Server will physically reorder the columns, having the fixed length columns first, then the variable length ones. (SQL Server Conf. in Orlando 2002 maybe.)

I do not remember if this was the case only when the table has a clustered index.

Once you understand the BITs, all the pieces come together

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

It was bugging me, so I dug around a little...

It was VSLive Orlando 2002, Kimberly Trip's "Designing for Performance" session...

The Data Row:

4 Byte Header +

Sum of all Fixed +

"Null Block" (do not know how long? 1 byte?) +

"Variable Block" +

Variable Data

Page:

96 Byte Header +

8096 Bytes of Data Rows

Seems each row will also need a 2 byte "Row Offset" entry.

Also, look in BOL for DBCC SHOWCONTIG.

I think for MAX optimization, no row should cross pages. You would have to construct your rows as fixed length, then including the overhead, have it's length devisible into 8096.

Once you understand the BITs, all the pieces come together