Row_overflow data was not converted to in_row data after changing or updating a table

Question

I learned about allocation units on the Sql server and learned about 3 types

• In_row_data LOB_DATA
• (LOB data).

• String overflow data.

  IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='TEST_ALLOCATION_UNITS') DROP TABLE TEST_ALLOCATION_UNITS CREATE TABLE TEST_ALLOCATION_UNITS( TEST_ID INT IDENTITY(1,1) ,NAME VARCHAR(4000) ,NAME1 VARCHAR(5000) ) GO INSERT INTO TEST_ALLOCATION_UNITS (NAME,NAME1) VALUES ('APPLE','BANANA') GO 500 

  I entered 500 entries in it

No, I ran the code and I got a result like this

 select s.type_desc before_alter from sys.allocation_units s join sys.partitions p on p.partition_id=s.container_id where p.object_id=object_id('test_allocation_units') 

Now I modify the table and reduce the size of both columns to 50 bytes each time and run the above select statement again

 alter table test_allocation_units alter column name varchar(50) alter table test_allocation_units alter column name1 varchar(50) select s.type_desc from sys.allocation_units s join sys.partitions p on p.partition_id=s.container_id where p.object_id=object_id('test_allocation_units') 

Question

According to my understanding, before changing the table, the total column size for this table was more than 8 Kb, so the distribution type was both In_row_data and Row_overflow_data. But after the alter instruction, i.e. Reducing the size of the column to 50 bytes, and then also the alloction unit were both In_row_data and Row_overflow_data strong>

clarify please.

early

Scenario 2

I tried with an empty table and ran the following code

 IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TEST_ALLOCATION_UNITS' ) DROP TABLE TEST_ALLOCATION_UNITS CREATE TABLE TEST_ALLOCATION_UNITS ( TEST_ID INT IDENTITY(1, 1) ,NAME VARCHAR(4000) ,NAME1 VARCHAR(5000) ) GO SELECT s.type_desc before_alter,s.total_pages,s.used_pages,s.data_pages FROM sys.allocation_units s JOIN sys.partitions p ON p.partition_id = s.container_id WHERE p.object_id = object_id('test_allocation_units') ALTER TABLE test_allocation_units ALTER COLUMN NAME VARCHAR(50) ALTER TABLE test_allocation_units ALTER COLUMN name1 VARCHAR(50) SELECT s.type_desc after_alter,s.total_pages,s.used_pages,s.data_pages FROM sys.allocation_units s JOIN sys.partitions p ON p.partition_id = s.container_id WHERE p.object_id = object_id('test_allocation_units') 

I did not add any records to the table, the results of the above script are shown in the figure.

Please tell me why, even after changing the size of the column, the distribution block was not changed?

Answer 1

To get the data "redistributed", you must run this command:

ALTER TABLE TEST_ALLOCATION_UNITS REBUILD

REBUILD advises SQL Server to redistribute the datapages in the HEAP table.

Answer 2

To answer your question

row_overflow data was not converted to in_row data after changing or updating a table

there has never been row_overflow data in the table, so there is nothing to convert when changing the column type.

---

Add a few more columns to the query:

 select s.type_desc ,s.total_pages ,s.used_pages ,s.data_pages from sys.allocation_units s join sys.partitions p on p.partition_id=s.container_id where p.object_id=object_id('test_allocation_units') 

You will see that only IN_ROW_DATA has a nonzero number of pages, and ROW_OVERFLOW_DATA reserved and 0 pages are used.

---

In any case, when you store the value "banana" in the varchar(5000) column, you only save 6 bytes per row (plus some overhead). Thus, changing this column type to varchar(50) does not change anything significant on the data pages.

There would be a difference in the number of pages a table occupies on disk if the column was of type char(5000) and then changed to char(50) . When you store the value "banana" in a char(5000) column, the value will be placed right with 4994 spaces before the declared column length, so here the declared column size has a direct effect.

Again, independently, these spaces will be stored in the line. If you try to create a table with char(4000) and char(5000) columns, the creation will fail because the maximum row size is 8060 bytes in SQL Server.

---

To see non-zero values ​​for ROW_OVERFLOW_DATA , you need to save long rows in varchar columns in a table. For instance:

 INSERT INTO TEST_ALLOCATION_UNITS (NAME,NAME1) VALUES (REPLICATE('APPLE', 800) ,REPLICATE('BANANA', 833)) GO 500 

In this case, we are trying to save 4000 bytes in name and 4998 bytes in name1 , which is only 8998 (plus some additional service ones), which is more than a maximum of 8060 bytes, so the off-line value is pressed.

---

I would recommend trying to add / remove multiple lines with short and long lines and check how the result of the view changes. This should help you understand the results of the review.