Systems Engineering and RDBMS

Wide Tables in SQL Server 2008

Posted by decipherinfosys on March 17, 2009

A couple of days back while discussing the new features of SQL Server 2008 within a group, one of the client DBA’s asked about “Wide Tables”.  He had read about it somewhere that besides the other types of tables (regular ones, system tables, temp tables, table variables and partitioned tables), these are new types of tables that are available in SQL Server 2008.  One of us had blogged before about sparse columns so I knew about those but the terminology of wide tables was new to me so I went about looking it up in SQL Server 2008 BOL.  Well, as the name states, these tables allow for a very large number of columns – as per BOL, it can have up-to 30000 columns, up-to 1000 indexes and up-to 30000 statistics.  So, the first thought that came to mind was – has the page size been made configurable in the case of SQL Server 2008 just like it is in the case of Oracle?  No – the page size is still the same and the max size for a single row is 8019 bytes.

Hmmm…so, what is the use of having those 30000 column flexibility then – surely, if I start using a large number of columns, we will easily exceed that limit.  That is where the sparse columns fit in (see the link above) – such wide tables make use of sparse columns so most of the data in the row is Null. The wide table has a column set which is essentially an untyped XML that combines all the sparse columns – you read more on the column sets in BOL here.

There are performance considerations of making use of wide tables – BOL covers them over here.  Other than making use of these for getting around the EAV issue or in applications where dynamic additions of columns are done to the tables (believe me, I have seen applications that do that – I don’t agree with the approach but just mentioning it over here since I have seen those in real live applications – all of those were applications which used them for configuration data only), don’t really know where else I would ever use these wide tables though.

Resources:

  • BOL entry on usage of sparse columns – here.
  • Microsoft MVP Paul Randal’s post – here.

Leave a Reply

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

 
%d bloggers like this: