For normal tables, SQL/Server has a hard limit of 1024 columns. This article explains what to do if your Salesforce table has more than 1024 columns.
SQL/Server will support up to 30,000 columns per table but to achieve this columns above the 1024 limit must be created as SPARSE columns. A sparse column is a column whose value is stored in a special column that contains an XML document. Naturally, sparse columns are more expensive to search and update than regular columns but they do provide a way to get past the 1024 limit.
The produced to fix a problem table is:
- Turn a table into a wide table by adding a new column to hold sparse column values.
- Identify which columns to change to sparse columns.
- ALTER the definition of the identified columns to be sparse.
The following example code creates a regular table and then turns it into a wide table and changes most of its columns to be sparse.
CREATE TABLE SparseExample (
id BIGINT IDENTITY
ALTER TABLE SparseExample ADD sparseColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS;
ALTER TABLE SparseExample ALTER COLUMN name VARCHAR(50) SPARSE;
ALTER TABLE SparseExample ALTER COLUMN address VARCHAR(255) SPARSE;
ALTER TABLE SparseExample ALTER COLUMN other VARCHAR(MAX) SPARSE;
INSERT INTO SparseExample(name,address,other) VALUES(‘Capstorm’, ‘Florida’, ‘Now is the time for all good men…’);
SELECT name, address,other, sparseColumns FROM SparseExample
For Additional Help
Google SQL Server Wide Table and you will find many helpful articles.