SQL Server - CREATE TABLE exceeds the maximum of 1024 columns

In SQL Server, a table can contain a maximum of 1024 columns. For the example, the following CREATE TABLE statement fails:

SQL Server:

  CREATE TABLE items
  (
    c1 CHAR(1),
    c2 CHAR(1),
    c3 CHAR(1),
    c4 CHAR(1),
    c5 CHAR(1),
 
    // ... Some columns are skipped for brevity
 
    c1024 CHAR(1),
    c1025 CHAR(1),
    c1026 CHAR(1),
    c1027 CHAR(1)
  );
 # Msg 1702, Level 16, State 1, Line 1
 # CREATE TABLE failed because column 'c1025' in table 'items' exceeds the maximum of 1024 columns.

But SQL Server allows you to define columns as SPARSE and they are not subject to 1024 column limit, and a table can have up to 30,000 SPARSE columns per table.

So you can overcome 1024 column limit using SPARSE columns as follows:

SQL Server:

  CREATE TABLE items
  (
    c1 CHAR(1),
    c2 CHAR(1),
    c3 CHAR(1),
    c4 CHAR(1),
    c5 CHAR(1),
 
    // ... Some columns are skipped for brevity
 
    c1024 CHAR(1) SPARSE,
    c1025 CHAR(1) SPARSE,
    c1026 CHAR(1) SPARSE,
    c1027 CHAR(1) SPARSE,
    colset XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
  );
  # Commands completed successfully.

Note that COLUMN_SET FOR ALL_SPARSE_COLUMNS column has to be additionally added to the table. It is a non-SPARSE column so it is subject to 1,024 column limit restriction.

You can insert rows into a table with SPARSE columns as usually:

SQL Server:

  INSERT INTO items (c1, c2, c1024, c1025) VALUES ('a', 'b', 'c', 'd')
  # (1 row affected)

But if you query the table, it additionally returns the COLUMN_SET column containing SPARSE columns values only in XML form:

SQL Server:

  -- Returns all columns and COLUMN_SET for SPARSE columns
  SELECT * FROM items;

Result:

For more information, see SQL Server Migration.