Row record greater than 8k

SQL Server 2005 is still using page 8096bytes, but unlike previous versions, it allows you to exceed the 8K limit. However, for a particular column, it is still not allowed to exceed the 8K limit, except for the LOB type. For example, you cannot define varchar (9000) or nvarchar (5000) types, and some say you can define varchar (max) or nvarchar (max), but unfortunately, these two new types are LOB types, with varchar, The essence of nvarchar is different.

We're back to the point, but we can declare a row over 8096bytes, such as MyTable (a varchar (1000), b nvarchar (4000)), which is allowed in 2005. So how do 2005 handle these too large lines internally? In fact, inside, it will first determine the size of your actual data, if you execute insert INTO mytable select ' A ', ' B ', the actual row size is 3bytes (does not contain page header information), SQL Server will have this line exists In_row_ This page is the fastest in the data type page. If you perform the INSERT INTO MyTable select ' A ', replacate (' B ', 4000), then the size is 8003bytes, over 8000bytes, (96bytes is page header information) then column B is reassigned to a Row_overflow_data type page, where the address information for the new page is recorded in the original In_row_data. That's why 2005 makes the row size break through the 8k limit.

So when the row size exceeds 8k, the design of the table structure should pay attention to improve the efficiency of data access, because the In_row_data speed is the fastest, row_overflow_data second, lob_data slowest (storage of large types of text,image, etc.), it is best not to let row Size beyond 8k to improve efficiency.

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/zbc1009/archive/2008/05/06/2404274.aspx