FIX: Column data may be deleted when you update another variable-length column in a table of a database upgraded from SQL Server 2005

Applies to: SQL Server 2012 DeveloperSQL Server 2012 EnterpriseSQL Server 2012 Standard


When you update a value in a variable-length column in a table of a database that was upgraded from Microsoft SQL Server 2005, data in another variable-length column in the same row is deleted.

This issue occurs in Microsoft SQL Server 2014 and Microsoft SQL Server 2012. The following scenario may expose a database to this issue. This list represents a sequence of events:

  • A database is created in SQL Server 2005.
  • In SQL Server 2005, you alter a table, and then you drop one or more variable-length columns at the end of the table definition.
  • You add new variable-length columns that have a similar data type and length of the dropped columns.
  • The database is upgraded from SQL Server 2005 to SQL Server 2014 or SQL Server 2012.
  • You later update the value of another variable-length column, and the updated data has to overflow to a new page.
In this scenario, the data in one or more of the newly added columns in the same row may be deleted.

You can use the script in the More Information section to verify the following:

  • Whether any tables in a given database have deleted variable-length columns
  • Whether additional variable-length columns have been created that have matching offsets
Note This issue is not discovered by DBCC CHECKDB.


Note This fix only prevents future occurrences of the issue.

Important If you are upgrading the database from SQL Server 2005 to SQL Server 2014 or SQL Server 2012, make sure that you refer to the "Workaround" section.

This issue was first fixed in the following cumulative update for SQL Server:

These are the latest cumulative updates for these program versions.

Note For more information about the latest SQL Server builds, see Where to find information about the latest SQL Server builds .


Important Apply this workaround when you upgrade the database from SQL Server 2005 to SQL Server 2014 or SQL Server 2012 and before you let UPDATE statements be run in the upgraded database.

To work around this issue if you drop columns in a table, make sure that you do either of the following before you update or delete rows:

  • Rebuild the clustered index by using ALTER INDEX (…) REBUILD
  • Rebuild the heap by using ALTER TABLE (…) REBUILD

More Information

You can use the following script to verify whether any tables in a given database have deleted columns that are potentially affected by this issue.

Note If the script does not return any rows, that means that this issue does not currently apply to any table in your system. 
SELECT DISTINCT OBJECT_NAME(sp.[object_id]) AS TableWithDroppedCols
FROM sys.system_internals_partition_columns sipc1 WITH (NOLOCK)
INNER JOIN sys.system_internals_partition_columns sipc2 WITH (NOLOCK)
ON sipc1.[partition_id] = sipc2.[partition_id] AND
sipc1.leaf_offset = sipc2.leaf_offset AND
sipc1.leaf_offset < 0 AND
sipc1.is_dropped < sipc2.is_dropped
INNER JOIN sys.partitions sp WITH (NOLOCK) ON sipc1.[partition_id] = sp.[partition_id];

Important This script should be used only for the purpose and scope of this article.


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.