Performance Considerations for Wide Tables

A wide table is a table with a column set. When you use wide tables, keep in mind the following performance considerations:

• Wide tables can define up to 30,000 columns. This increases the cost to maintain indexes on the table. Nonclustered indexes that are defined should be filtered indexes that are applied to data subsets. For more information, see Filtered Index Design Guidelines.

• Applications can dynamically add and remove columns from wide tables. When columns are added or removed, compiled query plans are also invalidated. We recommend that you design an application to match the projected workload so that schema changes are minimized.

• When data is added and removed from a wide table, performance can be affected. Applications must be designed for the projected workload so that changes to the table data are minimized.

• We recommend that the number of indexes on a wide table be limited to the indexes that are required by the business logic. As the number of indexes increases, so does the DML compile-time and memory requirement.

• Limit the execution of DML statements on a wide table that update multiple rows of a clustering key. These statements can require significant memory resources to compile and execute.

• Switch partition operations on wide tables can be slow and might require large amounts of memory to process. The performance and memory requirements are proportional to the total number of columns in both the source and target partitions.

• Update cursors that update specific columns in a wide table should list the columns explicitly in the FOR UPDATE clause. This will help optimize performance when you use cursors.