Shulou(Shulou.com)06/01 Report--

Insider on modifying table structure

Not all ALTER TABLE changes require SQL Server to change every row when ALTER TABLE is used.

SQL Server can execute ALTER TABLE in three basic ways

1. It may only need to change metadata

2. It may need to check all existing data to make sure it is compatible with changes, but only changes to metadata are required.

3. It may require physically changing each line.

In many cases, SQL Server can only change metadata (primarily viewing data through sys.columns) to reflect the new structure.

In particular, deleting a column, adding a null column, increasing the length of a variable-length column, or changing a non-nullable column to nullable does not change the original data.

Data is not touched when a column is deleted, meaning that disk space for that column is not reclaimed. When the row size of a table approaches or exceeds its limit, you may need to manually reclaim disk space for deleted columns.

You can reclaim space by creating or ALTER INDEX to rebuild the cluster index of the table, see Chapter 7, or by ALTER TABLE to rebuild the table, see Chapter 8.

Some table structure changes require checking the data but modifying it. For example, when you change a nullable column to non-nullable, SQL Server must first verify that there are no nulls in the column's data.

When a variable-length column is shortened, all existing data must be checked, and the ALTER TABLE command fails if any data is longer than the new limit. It is worth noting that changing a large table takes time.

Changing a fixed-length column to a shorter type, such as int to smallint, or char(10) to char(8), also checks that all data can be stored in the new type.

However, even if the new data type consumes fewer bytes, the data on the physical page is not modified.

If you create a table with int columns and 4 bytes per row, all rows will use the full 4 bytes. After changing the int type of a table to a smallint type, you insert data that is limited by the scope of the new type,

But the data is still 4 bytes, 2 bytes of smallint or not, which you can verify with dbcc page.

char(10) becomes char(8) Similar to before, data is still stored using 10 bytes, but insertion is limited to 8 bytes. Char(10) doesn't really become char(8) until the table is rebuilt.

Other changes to the table structure require SQL server to physically change each row; when it makes changes, it must write the appropriate records to the transaction log, so these changes can be very resource intensive for large tables.

An example of this type change is changing the data type of a column to a new type with a different internal storage representation.

Another negative effect of modifying table structure occurs when columns are modified to promote length. In this case, the old column is not actually replaced; instead, the new column is added to the table, and the DBCC page shows that the old data still exists.

You can browse the page dump for this scenario on your own, but you can see some unexpected behavior by looking at column offsets using the column detail query shown earlier in Listing 6-5.

First, create a table with all the fixed-length columns, including the smallint in the first position:

CREATE TABLE change

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

Now look at column offsets:

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');

RESULTS:

column_name column_id max_inrow_length system_type_id leaf_offset

------------- ----------- ------------------ -------------- -----------

col1 1 2 52 4

col2 2 10 175 6

col3 3 5 175 16

Now change smallint to int:

ALTER TABLE change

ALTER COLUMN col1 int;

Finally, run the column detail query in Listing 6-5 again to see that col1 now starts later in the row,

and none of the columns after the row header information starts at offset 4.

New columns created as a result of changes to the table occur even before any data is placed in the table:

column_name column_id max_inrow_length system_type_id leaf_offset

------------- ----------- ------------------ ---------------- -----------

col1 1 4 56 21

col2 2 10 175 6

col3 3 5 175 16

Another drawback to SQL server behavior when old columns are not actually deleted is that row sizes are now more tightly constrained. Row sizes now include old columns, which are no longer available or visible (unless DBCC PAGE is used).

For example, if you create a table with a pair of large fixed-length character columns,

The char (2000) column can be changed to char (3000) as follows:

CREATE TABLE bigchange

(col1 smallint, col2 char(2000), col3 char(1000));

ALTER TABLE bigchange

ALTER COLUMN col2 char(3000);

At this point, the row length should only exceed 4000 bytes due to 3000 byte columns, 1000 byte columns, and smallint. However, if you try to add another 3000-byte column, it fails:

ALTER TABLE bigchange

ADD col4 char(3000);

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'bigchange' failed because the minimum row size

would be 9009, including 7 bytes of internal overhead. This exceeds the

maximum allowable table row size of 8060 bytes.

However, simply creating a table with two 3000-byte columns and 1000-byte columns does not cause any problems:

CREATE TABLE nochange

(col1 smallint, col2 char(3000), col3 char(1000), col4 char(3000));

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.