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))
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
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.
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:
(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 http://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.
Will this also prevent altering another (non-vardecimal) column in a table that already has a vardecimal column, if that alteration makes the maximum possible length > 8060?
Out of curiosity, why not use row-overflow instead of failing to allow the change? Is it to avoid surprising performance hits, when the intent of using vardecimal was to save disk space?
Seems like it would be more consistent to give a warning on the alteration, but let the customer decide if he wants to make the tradeoff. The customer might know that actual overflow will never or rarely happen.
Or, was there just not time to implement it this way?
Out of row value needs 24 bytes for pointer and only variable length columns can be stored out of row. So in the example that I gave, none of the columns can be stored out of row.
thanks for your interest.