How do you create a wide table in SQL Server 2008? and what are its limitations on use?

Question

I was reading the Maximum Capacity Specifications for SQL Server and I came across the designation of a wide table. It's different from a standard table in that is can have as many as 30,000 columns as opposed to a normal (narrow) table that is limited to the more familiar 1024 columns. I googled for wide table, but nothing seem to come up relevant. Does this new table type have a more formal name???

So why do we have two different types of tables, how do you create this special table and what are the limitations of using this table that can seemingly hold more data ? anhyone know ?

Solution 1

A wide table is a table that uses column sets and sparse columns. It still follows the same width restrictions per row (8019 bytes) - so you'd typically only use it when your columns are mostly all nulls.

See here for more info on...

• special table types in SQL 2008
• column sets
• sparse columns

Solution 2

"To create or change a table into a wide table, you add a column set to the table definition."

From here

Solution 3

You usually do not want to do this however! There are size restrictions on the rows and it can be slower to retrieve data than if you use related tables (even those with one-to-one relationships). I've never yet seen an instance where this was a better idea than related tables.

Solution 4

Another limitation is that wide tables don't work with transactional or merge replication. See the "SQL Server Technologies That Support Sparse Columns" section here: http://msdn.microsoft.com/en-us/library/cc280604(v=sql.105).aspx

Solution 5

CREATE TABLE [UR_DB].[dbo].[DesiredTableName]
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL,
MarketingProgramID int SPARSE NULL,
SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

Creating Complex Test Databases - Creating a Table with more than 1,024 Columns

View more solutions

Comments

[jvanderh]

do you mean more than 30,000 columns instead of roads?

[alpav]

This reference says: "Transactional replication supports sparse columns, but it does not support column sets", could you clarify your answer and also see my relevant unanswered question dba.stackexchange.com/questions/59476/… ?

[Josh Yeager]

To make a table into a wide table, you must create sparse columns and also add a column set. If you don't create a column set your table will still be limited to 1024 columns.

[Henrik Høyer]

The 8K limit is for pre SQL Server 2016 versions, see [link]technet.microsoft.com/en-us/library/ms186981(v=sql.105‌​).aspx

[Scott Ivey]

SQL 2016 does still have a bytes per row limit of 8060, but the row overflow does let you store more data. This isn't new in 2016, and has been around for quite a while. msdn.microsoft.com/en-us/library/ms143432.aspx

[Jeff Moden]

Where did you come up with the number 8019 as a page width restriction?

[Scott Ivey]

not sure where that came from - too long ago. Current width restriction for a row is 8060 bytes. closest I can find is this page saying 8018 width limit for sparse columns - docs.microsoft.com/en-us/previous-versions/sql/…

[AcePL]

Normally I agree, but have you considered all scenarios? I have, for example, an export to excel which requires staging the data in the table. 1300+ columns, mostly nulls. When nothing else works...

[Janin]

Thanks for this! I was looking for an example which creates the table with sparse columns so I can put in all the columns in one statement.