A big question in SQL Server - create a table with more than 1024 columns?
IT was an interesting question to ask/know about create a table with more than 1024 columns?
If you try to do so then obvious error:
CREATE TABLE failed because column '<ColName>' in table 'MaxSparseColumnTest' exceeds the maximum of 1024 columns.
To reproduce the behaviour use the Query posted by fellow MVP - Aaron Bertrand:
IF EXISTS(SELECT * FROM SYS.tables WHERE name = 'MaxSparseColumnTest')
DROP TABLE MaxSparseColumnTest
DECLARE @i INT, @SQL VARCHAR(MAX)
SET @i = 1
SET @SQL = 'CREATE TABLE MaxSparseColumnTest ('
WHILE @i < 1026
BEGIN
SET @SQL = @SQL + 'COL' + CAST(@i AS VARCHAR(5)) + ' INT SPARSE NULL, '
SET @i = @i + 1
END
SET @SQL = LEFT(@SQL, LEN(@SQL)-1) + ')'
EXEC(@SQL)
By design when you perform a CREATE TABLE task, the creation of more than 1024 columns in a table requires the use of a column_set column. So without having a column-set the limit of total number of columns is 1024. The new version of SQL Server includes this 'sparse columns' that are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values, these are god when you need to save the table space having millions of data. To give you a brief note from BOL on column_set & filtered indexes when SPARSE COLUMNS are used:
Sparse columns can be used with column sets and filtered indexes:
- Column sets
INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. This column is called a column set.
- Filtered indexes
Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index.
I like the characteristics of sparse columns as the DB engine uses SPARSE keyword in a column definition to optimize the storage of values in that column which offers the advantage of column value with NULL for any row in table require no storage. In the recent times I have experimented usage of SPARSE columns within a financial based application design, to add test data (more than million rows) to the tables for the storage implications and the next question was how to find/check the storage results? I was expecting to see a DMV that defines the required value, this is what I have found in BOL from system catalog sys.allocation_units and DMV - sys.dm_db_partition_stats. Thanks to MS CAT professional (sorry, forgot the name) for the note via email (on the phone) to get such information using following query:
SELECT OBJECT_NAME(object_id) AS objectName
,in_row_data_page_count
,in_row_reserved_page_count
,in_row_used_page_count
,used_page_count
,reserved_page_count
,row_count
,totalPages
,dataPages
,usedPages
FROM sys.dm_db_partition_stats AS s
INNER JOIN (
SELECT container_id, SUM(total_pages) AS totalPages, SUM(data_pages) AS dataPages, SUM(used_pages) AS usedPages
FROM sys.allocation_units
GROUP BY container_id
) AS au ON s.partition_id = au.container_id
WHERE s.object_id = OBJECT_ID('dbo.ProductSparse', 'U')
OR s.object_id = OBJECT_ID('dbo.ProductNotSparse', 'U');
Going back to discussion about having the column_set the limits are 1024 non-sparse columns (that includes column_set & computed columns) and 30,000 columns to total, again BOL clearly states this information. To my amusement Kalen Delaney's blog has guided me to the right direction on handling the situation when there is a need to create table with SO MANY columns. As the feature of SQL 2008 SPARSE column allows upto 30,000 columns in a table using the script provided in her blog post (p.s: I have had technical issues in referencing the actual blog post by Kalen, so posting the extract):
*******************
You can replace the 100 (for the value of @numcols) by the number of columns you'd like, but the script will create one more, because it starts with a ID int identity column. So the script as is will create a table with 11 columns. In the loop that adds columns to the creation string, I had three different datatypes of columns that I can use. One third of the columns will be type int with a default of 0, one third will be char(5) with a default of 'hello' and one third will be varchar(25) with a default of 'this is a longer message'. You can modify this however you like. You can change the datatypes within the loop, or add more conditionals and change expression to use modulo of some other number.
DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 100;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);
SELECT @create = 'CREATE TABLE ' + @tabname +
' (ID int IDENTITY, ';
SELECT @col = 1;
WHILE @col < @numcols BEGIN
IF (@col % 3) = 0
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
' int default 0,';
IF (@col % 3) = 1
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
' char(5) default ''hello'',';
IF (@col % 3) = 2
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
' varchar(25) default ''this is a longer message'',';
SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
' int default 0);'
PRINT @create
EXECUTE (@create)
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);
To use this script to test the creation of a table with more than 1024 columns, I knew some (or most?) of the columns would need to be sparse. In addition, I found this Connect entry which indicated that if you do have more than 1024 columns, the table must have a column set, so it was easy enough to make that the last column that is added to the create string after the loop.
DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 1200;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);
SELECT @create = 'CREATE TABLE ' + @tabname +
' (ID int IDENTITY, ';
SELECT @col = 1;
WHILE @col < @numcols BEGIN
IF (@col % 3) = 0
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
' int sparse,';
IF (@col % 3) = 1
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
' varchar(5) sparse,';
IF (@col % 3) = 2
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
' varchar(25) sparse,';
SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
' xml column_set for all_sparse_columns);';
PRINT @create
EXECUTE (@create)
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);
Running this script, I was able to create a table with 1200 columns.
***************************
To my surprise on this topic I have found that Getting more than 1024 columns in a table - SimonS Blog on SQL ... is a killer!