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 |
|
aakcse
Aged Yak Warrior
570 Posts |
 Posted - 2009-07-09 : 03:28:47
|
| What are the differenct between LOB_DATA and ROW_OVERFLOW_DATAAllocation unit.by defination I am not clear, I m mixing up these two...plz help..Lob_Data: Used to store Large Object (LOB) data, such as text, ntext, xml, image, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined data types. Row_Overflow_Data: Used to store data pages when the variable data types—varchar, nvarchar, varbinary, and sql_variant data columns—that exceed the 8,060 bytes that can fit onto a single data page.Does it means that.. nvarchar(8000) taking up entire page hence this column is pushed into Row_Overflow_data and a pointer is placed inthe page which points to this Overflow page.??Regards,A |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-09 : 03:37:10
|
| Hi LOB_DATAWhen a table or index has one or more LOB data types, one LOB_DATA allocation unit per partition is allocated to manage the storage of that data. The LOB data types include text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined types.ROW_OVERFLOW_DATAThis allocation unit contains zero (0) pages until a data row with variable length columns (varchar, nvarchar, varbinary, or sql_variant) in the IN_ROW_DATA allocation unit exceeds the 8 KB row size limit. When the size limitation is reached, SQL Server moves the column with the largest width from that row to a page in the ROW_OVERFLOW_DATA allocation unit. A 24-byte pointer to this off-row data is maintained on the original page. IN_ROW_DATA For every partition used by a table (heap or clustered table), index, or indexed view, there is one IN_ROW_DATA allocation unit that is made up of a collection of data pages. This allocation unit also contains additional collections of pages to implement each nonclustered and XML index defined for the table or view.System view you can use this sys.system_internals_allocation_units-------------------------Your time is a valuable resource. |
 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-09 : 03:45:08
|
| Hisys.partitions A heap has a row in sys.partitions with index_id = 0.Each table that has at least one LOB column also has a row in sys.partitions with index_id > 250. The first_iam_page column points to the chain of IAM pages that manage the pages in the LOB_DATA allocation unit.Each nonclustered index created for a table or a view has a row in sys.partitions with index_id > 1. -------------------------Your time is a valuable resource. |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2009-07-09 : 03:46:58
|
| ROW_OVERFLOW_DATAThis allocation unit contains zero (0) pages until a data row with variable length columns (varchar, nvarchar, varbinary, or sql_variant) in the IN_ROW_DATA allocation unit exceeds the 8 KB row size limit. When the size limitation is reached, SQL Server moves the column with the largest width from that row to a page in the ROW_OVERFLOW_DATA allocation unit. A 24-byte pointer to this off-row data is maintained on the original page. it is only that column moved and not the entire row..and it is moved to the chain of pages.. the only differenc here I see wrt Lob_DATA is in lob_data the colum is moved at the time of insert where as in row_over_flow it is moved after the page size is reached due to that particular cloumn and even if the entier row is moved to new page it wont fit due to this col,hence this column itself is moved.am I rt.?Regards, |
|
|
|
|
|
|
|
|
Allocation unit