Did you know? -- Altering the length of a fixed-length column

You may be well aware that if you alter the length of a column to a larger value, that SQL Server doesn’t go through all the rows at the time you issue the ALTER. The ALTER is a metadata only change, and the actual movement of data doesn’t happen until a row is updated.

However, SQL Server does not reuse the original bytes in the row when a row’s length is altered. You may end up not being able to use the full allowable row length if you’ve altered column lengths.

Here’s the example. Create a table with two large char columns and a smallint. The initial length of each row will be just a bit more than 3000 bytes, out of a maximum row length of 8060 bytes.

CREATE TABLE bigchange

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

GO

Now alter the second column to increase its length to 3000 bytes. The length of a row should now be just a bit more than 4000 bytes.

ALTER TABLE bigchange

   ALTER COLUMN col2 char(3000);

GO

Now try and add another 3000 byte column, which should bring the length to just over 7000 bytes. This will fail:

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, if you just create a table with two 3000-byte columns and a 1000-byte column, there will be no problem.

CREATE TABLE nochange

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

GO

Here’s a query you can run to see the metadata for where each column will start. Run it against the bigchange table:

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

GO

Here’s my results:

column_name   column_id   max_inrow_length system_type_id leaf_offset

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

col1          1           2                52             4

col2          2           3000             175            3006

col3          3           1000             175            2006

Note that col1 is only 2 bytes long, and starts at offset 4 (after the row header information) but that the next column doesn’t start until offset 2006. Bytes 6 – 2005, the original 2000 bytes for col2, are not used for anything now.

So be careful when using large datatypes, especially if you want to make them fixed length instead of variable length.

-- Kalen