- You can turn on the trace flag 647 by using the startup parameter. For information, see Database Engine Service Startup Options.
- You can run the following query (Please replace <table name> with your actual table name) to test the particular table before the operation on a production system, and plan the effect of this issue. If the result is greater than 8,060, your table is subject to this issue. Additionally, if you have dropped columns, you have to alter the table with the rebuild option. Rebuilding the table can make the query return correct results.
1+1+2 + 2 +
(case when sum (case when leaf_offset < 0 then 1 else 0 end) > 0 then 2 else 0 end) +
( (count (*) + 7)/8 ) + count (case when leaf_offset < 0 then 1 else null end) * 2 +
sum( case when max_length=-1 then 24 else max_length end)
from sys.system_internals_partition_columns col join sys.partitions par on col.partition_id = par.partition_id
where object_id = object_id ('<table name>') and index_id in (0,1) and partition_number =1
- You can consider adding variable length columns or sparse columns when your application frequently adds new columns to the table. It is a metadata-only operation even if the total size of all columns exceeds 8,060 bytes.
Article ID: 2986423 - Last Review: Dec 17, 2014 - Revision: 1