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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-20 : 08:38:02
|
| John Marshall writes "I'm trying to make an informed decision on some table design issues and need a clearer understanding of how space is allocated for VarChar fields. I've studied what BOL has had to say and looked through some related postings in this group via google.I understand that under SQL Server 2000 record size is limited to 8060 bytes. Pages in SQL Server are 8K and rows must fit within one page (hence the 8060 byte limit). I also understand that SQL Server will store as many rows as possible in an 8K page. What I don't know is if the maximum space for a VarChar is "reserved" for the row when storing in a page so the row doesn't have to be moved to a different page later if the size increases or if SQL Server will squeeze as many rows as possible into a page and then if a row increases in size will move it to a different page.For example, say I have a row with many VarChar columns. The maximum size is 4,000 bytes. If the columns were filled out to their maximum size we could fit 2 rows in a page. Easy enough. Now what if the columns are not filled with their maximum size but instead with data that brings the row size to 2,000 bytes. SQL Server _could_ fit 4 of these rows into a page. My question is _would_ SQL Server put 4 of these rows into a page and then move a row out if it grew too large for the page or would it still only store 2 rows in the page since they might grow to 4,000 bytes each?Regards,John" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-20 : 09:11:35
|
| SQL Server will only store the actual data size in a varchar column, plus a few bytes overhead. If you have a varchar(4000) column with 2000 bytes in it, only 2000 bytes are used. If you have 4 rows like this on a data page, and you update one or more to the maximum 4000 bytes, the data page will be split automatically. Extra data pages will be allocated and the affected rows will be copied to the new pages.Books Online has several entries under "pages", take a look there, I'm pretty sure they describe the exact mechanics of what happens during a page split. Also take a look at the book Inside SQL Server 2000 by Kalen Delaney. Unless she's radically changed the book there should be a few pages in there with the behind-the-scenes stuff of a page split. |
 |
|
|
|
|
|
VarChar & Row/Page Storage