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

Eg.

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.

Ref: MSDN

Advertisements

Comment please...

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

WordPress.com Logo

You are commenting using your WordPress.com 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