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 |
|
hernan93
Starting Member
3 Posts |
 Posted - 2006-11-28 : 10:43:21
|
| In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off. For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?? |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-28 : 11:13:31
|
| Pages are always 8K and SQL Server places as many rows on a page as possible. So if you have a row that is 2K and you insert a new row that is 4K into the same table, SQL Server will put it on the same page and the page will now have 2K remaining. If you then insert a row that is 3K, it will go on a new page. (If you have a clustered index then rows must be placed in specific positions on specific pages, so it is more likely that a new page will need to be used, but SQL Server will always put as many rows on a page as possible.)Note that rows are never split across multiple pages although this new ROW_OVERFLOW_DATA capability does mean that an overflow page can be used for very large rows.As far as I know you cannot turn this off, and I can't see that you'd really want to. In most tables rows are nowhere near 8K in length and 10s or even 100s of rows are stored on each 8K page. |
 |
|
|
|
|
|
abaut 8KB limit