Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2005-01-03 : 13:40:00
|
Ok. I took this from BOL. quote:
The start of each page is a 96-byte header used to store system information, such as the type of page, the amount of free space on the page, and the object ID of the object owning the page.Rows cannot span pages in SQL Server. In SQL Server 2000, the maximum amount of data contained in a single row is 8060 bytes, not including text, ntext, and image data.
A page is 8KB (8196b) and the header is 96b. BOL states that a row cannot span across pages and the maximum size of a row is 8060b. 8060b + 96b = 8156b. One page = 8196b. What does SQL Server do with the other 40 bytes?Thanks.DanielSQL Server DBA |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-01-03 : 14:50:18
|
quote:
Originally posted by spirit1
well this will give you some pointers:http://www.nigelrivett.net/PageStructure.htmlGo with the flow & have fun! Else fight the flow 
Well not really. It's an intresting page though.I just wonder if SQL Server fills up the rest of the page with something else. Granted 40 bytes is not much, but I just want to know what SQL Server does with it. Thanks.DanielSQL Server DBA |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-03 : 14:54:15
|
all those bytes going to waste.... what a shame...  Go with the flow & have fun! Else fight the flow |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-03 : 15:31:43
|
| Actually an 8K page is 8192 bytes, so only 36 bytes are left over. A data page still needs a row offset table, with at least 2 bytes per row, so that's only 34 bytes "wasted". This doesn't count any null bitmaps for nullable columns, or the overhead needed for variable-length columns.There's an entry in Books Online for "estimating table size" that describes exactly how the space gets utilized for the columns, nulls, and varying lengths. I'm pretty sure that if you go through all the combinations of columns size, number, nullability and such via those formulas, 8060 is the most you can get based on the overhead and rounding involved. |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-01-03 : 15:44:09
|
quote:
Originally posted by robvolk
Actually an 8K page is 8192 bytes, so only 36 bytes are left over. A data page still needs a row offset table, with at least 2 bytes per row, so that's only 34 bytes "wasted". This doesn't count any null bitmaps for nullable columns, or the overhead needed for variable-length columns.There's an entry in Books Online for "estimating table size" that describes exactly how the space gets utilized for the columns, nulls, and varying lengths. I'm pretty sure that if you go through all the combinations of columns size, number, nullability and such via those formulas, 8060 is the most you can get based on the overhead and rounding involved.
Thanks Rob. That's some pretty good info. Your posts are always good and sometimes I think you must have written SQL Server for Bill Gates.  DanielSQL Server DBA |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-03 : 17:22:48
|
| 8060 is the max data length - i.e. the sum of the length of all columns in the row.If you look athttp://www.mindsdoor.net/SQLAdmin/PageStructure.htmlYou will see all the extra space that is taken up with the internal representation of that row.It is possible that less than 8060 bytes could overflow a rowseehttp://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
dumb question about pages