Max antal kolumner i en tabell?

Ibland behöver man ha många kolumner i en tabell, men hur många kan man maximalt ha i SQL Server? Som med så mycket annat är svaret “Det beror på”. Maximalt antal vanliga kolumner är 1024, men max radstorlek är 8019 bytes (om man inte använder BLOB’ar). Men om man använder sig av en så kallad Wide Table, och definierar ett Column Set med Sparse Columns, kan man ha så mycket som 30000 kolumner i en tabell. Fortfarande är maxstorleken på en rad 8019 bytes, så det förutsätter att de flesta kolumnerna är NULL om man har så många kolumner. Mer information om Column Sets: (Från Books Online)

– Sparse columns and a column set can be added as part of the same statement.
– The column set cannot be changed. To change a column set, you must delete and re-create the column set.
– A column set cannot be added to a table if that table already contains sparse columns.
– A column set can be added to a table that does not include any sparse columns. If sparse columns are later added to the table, they will appear in the column set.
– Only one column set is allowed per table.
– A column set is optional and is not required to use sparse columns.
– Constraints or default values cannot be defined on a column set.
– Computed columns cannot contain column set columns.
– Distributed queries are not supported on tables that contain column sets.
– Replication does not support column sets.
– Change data capture does not support column sets.
– A column set cannot be part of any kind of index. This includes XML indexes, full-text indexes, and indexed views. A column set cannot be added as an included column in any index.
– A column set cannot be used in the filter expression of a filtered index or filtered statistics.
– When a view includes a column set, the column set appears in the view as an XML column.
– A column set cannot be included in an indexed view definition.
– Partitioned views that include tables that contain column sets are updatable when the partitioned view specifies the sparse columns by name. A partitioned view is not updatable when it references the column set.
– Query notifications that refer to column sets are not permitted.
-XML data has a size limit of 2 GB. If the combined data of all the nonnull sparse columns in a row exceeds this limit, the query or DML operation will produce an error.