How to have 30k columns in a table – Wide Tables and sparse columns

Sparse Columns

These are columns with an optimized storage for NULL values.


  • The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the value requires no storage.
  • Catalog views for a table that has sparse columns are the same as for a typical table. The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.

  • Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.

  • The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. The bits that are returned by the COLUMNS_UPDATED function are as follows:

    • When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.

    • When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.

    • For insert operations, all bits are set to 1


Create Table TestSparse(ID Int,EName Varchar(100),Address varchar(20) SPARSE NULL )

Wide Tables

Wide tables are one of a kind of special tables types. Wide table has defined column set and uses sparse columns to increase the maximum number of columns a table can have to 30,000. Maximum number of index will be 1000 and Maximum number of statistics will be 30,000. The maximum size of a column in a wide table will be 8019 bytes. That means most of the columns in a row should be null if we use maximum number of columns in a wide table. You can only have maximum 1024 non sparse columns in a wide table.

Using a wide table increases the requirement of run time and compile time memory which will affect the performance.



Comment please...

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s