Boundary conditions for enabling vardecimal storage format

Have you ever tried updating a variable length column and fail? Well, it can happen if the modified row cannot fit on the page. One simple example of this as follows

create table boundary (c1 char(8000), c2 char(20),

c3 varchar(23), c4 decimal(38,2))

go

When you create this table, it gives you the following warning

Warning: The table "boundary" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

-- this is the max allowed value

insert into boundary values ('a', 'b', replicate ('1', 12), 0.0)

-- this update fails with the error

Msg 511, Level 16, State 1, Line 1

Cannot create a row of size 8061 which is greater than the allowable maximum of 8060.The statement has been terminated.

update boundary set c3 = replicate('1', 13)

However, if you update a fixed length column value, you will never get the error 511 because the fixed length value, by definition, retains the same size regardless of the value. Most applications that update a fixed length column value don’t check for 511 error.

You may wonder what happens when we enable vardecimal storage format on a table? Since the numeric/decimal data is now stored using variable length storage, an update to decimal/numeric value can potentially fail just like it failed when updating varchar column in the example before. Since applications are not expecting updates to decimal/numeric column to fail (unless of course there are constraints defined on the decimal/numeric value), the application may encounter unexpected failure. To prevent this from happening, SQL Server only allows enabling vardecimal storage format on a table if and only if it can gurantee that updates to decimal/numeric value will nevel fail with error 511. So for the table in the above example, if I enable vardecimal storage format as follows

sp_tableoption 'boundary', 'vardecimal storage format', 1

go

You will get the following error.

Msg 1721, Level 16, State 2, Procedure sp_tableoption, Line 129

Altering table 'boundary' has failed because the row size using vardecimal storage format exceeds the maximum allowed table row size of 8060 bytes.

Reason:

For the insert above, since the decimal column as 0.0 value, it takes only 2 bytes (offset array) of storage in Vardecimal storage format. Had SQL Server allowed Vardecimal storage format on ‘boundary’ table, then the following update to decimal data can fail:

Steps:

(1) Update column c3 to ‘1234567890124567890’. When you do that, the row size becomes 8053 bytes.

(2) Now update the decimal column c4 to max digits (38) allowed which requires 18 additional bytes of storage but we have only space for (8060 - 8053) = 7 bytes

Please ignore the size computations, if you are not familiar with it but the point is that SQL Server guarantees that your update to decimal/numeric will not fail with error 511. You can read https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/23/644607.aspx for more details on row format.

Since tables defined in most application schemas, the row size is much less than 8060, we don’t need to concern ourselves with this issue. But it is reassuring to know that your application will not be caught off guard when Vardecimal storage format is enabled.

Besides this, SQL server allows enabling Vardecimal storage format on a table only if it can guarantee that you can always disable it.