SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Allocation unit
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 07/09/2009 :  03:28:47  Show Profile  Reply with Quote
What are the differenct between LOB_DATA and ROW_OVERFLOW_DATA
Allocation 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 - 07/09/2009 :  03:37:10  Show Profile  Send rajdaksha a Yahoo! Message  Reply with Quote
Hi

LOB_DATA
When 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_DATA
This 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.
Go to Top of Page

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 07/09/2009 :  03:45:08  Show Profile  Send rajdaksha a Yahoo! Message  Reply with Quote
Hi

sys.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.
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 07/09/2009 :  03:46:58  Show Profile  Reply with Quote
ROW_OVERFLOW_DATA
This 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,

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000