September 15, 2011 at 8:59 am
Concensus is that its better to not have rows spill onto multiple pages.
But one thing i cant find out is what if your row only takes up 75% of a page, will the next row be split across the remaining 25% and the next page?
If so, then is it optimal to have a row take up an entire page, if its anywhere near the size of the page? you could ensure the row is always the size of the page using set size nchar or char where necessary?
EDIT: since found out that rows max size is just under the page size anyway, and anything over that will be on an overflow page. Also each page contains a row offset array of entries to the start of each row so I now know sql server will store as many rows as possible on one page.
September 15, 2011 at 9:28 am
winston Smith (9/15/2011)
Concensus is that its better to not have rows spill onto multiple pages.But one thing i cant find out is what if your row only takes up 75% of a page, will the next row be split across the remaining 25% and the next page?
Rows can't cross pages.
There's an overflow option for varchar/nvarchar, where if they get too large they'll be pushed out much like a LOB column, but the actual row is always on one page only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2011 at 9:29 am
In answer to your question, no. If the row takes up > 50% of the page, you get 1 row per page.
If you use varchars, you might end up with 4 rows on 1 page, 1 on another, 3 on another, etc.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply