Notes for modifying the field length in the production machine

In addition to some restrictions, see
MS-help: // Ms. sqlcc. V9/ms. sqlsvr. v9.zh-CHS/tsqlref9/html/f1745145-182d-4301-a334-18f799d361d1.htm about modifying column restrictions. When modifying columns that can be modified, pay attention to the following two issues:

1. When the length of a field is adjusted, a large amount of system resources are occupied. Therefore, it cannot be adjusted at the production time.
   When reducing the length of a field, the system first checks whether all the records in the original field are within the permitted range and performs a table scan. When increasing the field length, the database engine will first create a physical table, import the records of the original table to the new table, then delete the original table, and rename the physical table. Create constraints, indexes, and other objects in the table, which is also a resource-consuming operation. This will not only affect the modified table, but also affect the table with foreign key reference.
   You can see the corresponding script when modifying it on the graphic management interface. This occupies more system resources.
2. Whether it is increasing or decreasing the field length, disk space is wasted.
   This is why so many scripts are generated when the field length is added when the graphic management interface is used?
   The following script demonstrates the disk space waste when fields are directly adjusted using alter column. These wasted space will not be reused until you re-create the clustered index.

Use tempdb;

Go

Create
Table change

(Col1 smallint, col2 char (10), col3 char (5 ));

Go

-- View the offset of each field on the page. The leaf_offset field is now incremental.

Select C. Name as column_name, column_id, max_inrow_length, PC. system_type_id, leaf_offset

From
SYS. system_internals_partition_columns PC

Join
SYS. partitions P

On P. partition_id = pc. partition_id

Join
SYS. Columns C

On column_id = partition_column_id

And C. object_id
= P. object_id

Where p. object_id = object_id ('change ');

Go

Why is this result? First, let's take a look at how records are stored in tables.

For a record with a variable length and a fixed length column, the content of its fixed length field is stored internally, and the field content of the Variable Length Column is stored later. As you can see, the first four bytes are the two status bits and the length of the total length of the record fields (occupying 2 bytes ). Therefore, the first fixed-length column starts with 4th offset bits, because col1 is smallint occupies 2 bytes, so col2 starts from 6th bytes, and col2 occupies 10 bytes, therefore, col3 records data starting from 16th bytes and occupies 5 bytes. Now let's take a look at the distribution of records on the disk:

Select
Object_name (P. object_id)
As tablename, I. Name as indexname,

Rows, A. type_desc as page_type_desc,

Total_pages as pages, first_page

From
SYS. partitions P

Join
SYS. system_internals_allocation_units

On P. partition_id = A. container_id

Join
SYS. Indexes I

On P. index_id = I. index_id and P. object_id
= I. object_id

Where p. object_id = object_id (N 'change ')

On my machine, I got the following results:

After conversion, the first page of the change table is displayed on 120th of the first data file.

DBCC traceon (3604)

Go

DBCC page (tempdb, 1,120, 3)

Go

Below we modify the column length:

-- Increase the field length from smallint to int.

Alter
Table change

Alter
Column col1 int;

Go

Run the above script again to get the following results:

We can see that col1 is recorded from 21st bytes, and the two bytes before col2 are not used. To verify this, we first change the value of col1 to 3.

Update change set col1 = 3 where col1 = 2;

Go

In this case, we can view the data distribution on the disk and find that the original two bytes are still there.

In this case, only after the clustered index can be created on the table can the waste space be truly released.

Create
Unique
Clustered
Index pk_change on change (col1 );

Go

When you view the data distribution of the change table again, the first page is no longer the original page. View the content on the first page again and get the following results:

If you carefully observe the third byte (the total length of the record-length field), it is now changed from 19 (decimal: 25) to 17 (decimal: 23 ), this indicates that two wasted bytes have been released. In this case, you can delete the clustered index in the same way as when there is an index.

When you increase the field length, you will also face the above problems. The test method is the same as above and will not be tested here. Therefore, this is why SQL Serer generates so many scripts when you modify the field length using the graphic interface. It is used to create a new physical table, import the original records, delete the original table, and rename the new table to the name of the original table. Finally, create indexes, constraints, and other related objects for the table.

For more information, see the internal working method of data storage insider and record modification in sqlserver storage engine.