How many columns are too many for a SQL Server 2005 table?

Question

I have a request to allow a dynamic table to have 1000 columns(randomly selected by my end users). This seems like a bad idea to me. It's a customizable table so it will have a mixture of varchar(200) and float columns(float best matches the applications c++ double type). This database is mostly an index for a legacy application and serves as a reporting repository. It's not the system of record. The application has thousands of data points very few of which could be normalized out.

Any ideas as to what the performance implications of this are? Or an ideal table size to partition this down too?

Since I don't know what fields out of 20k worth of choices the end users will pick normalizing the tables is not feasible. I can separate this data out to several tables That I would have to dynamically manage (fields can be added or drooped. The rows are then deleted and the system of record is re parsed to fill the table.) My preference is to push back and normalize all 20k bits of data. But I don't see that happening.

Answer 1

I have to disagree with everyone here.....I know it sounds mad but using tables with hundreds of columns is the best thing I have ever done.

Yes many columns frequently have null values; Yes I could normalise it to just a few tables and transpose; Yes it is inefficient

However it is incredibly fast and easy to analyze column data in endless different ways

Wasteful and inelegant - you'll never build anything as useful!

Answer 2

Did you think of viewing your final (1000 columns) table as the result of a crosstab query? Your original table would then have just a few columns but many thousand records.

Can you please elaborate on your problem? I think nobody really understand why you need these 1000 columns!

Answer 3

Whenever you feel the need to ask what limits the system has, you have a design problem.

If you were asking "How many characters can I fit into a varchar?" then you shouldn't be using varchars at all.

If you seriously want to know if 1000 columns is okay, then you desperately need to reorganize the data. (normalization)

Answer 4

MS SQL Server has a limit of 1024 columns per table, so you're going to be running right on the edge of this. Using varchar(200) columns, you'll be able to go past the 8k byte per row limit, since SQL will store 8k on the data page, and then overflow the data outside of the page.

SQL 2008 added Sparse Columns for scenarios like this - where you'd have a lot of columns with null values in them.

Using Sparse Columns http://msdn.microsoft.com/en-us/library/cc280604.aspx

Answer 5

This will have huge performance and data issues. It probably needs to be normalized.

While SQl server will let you create a table that has more than 8060 bytes inteh row, it will NOT let you store more data than that in it. You could have data unexpectedly truncated (and worse not until several months later could this happen by which time fixing this monstrosity is both urgent and exptremely hard).

Querying this will also be a real problem. How would you know which of the 1000 columns to look for the data? Should every query ask for all 1000 columns in the where clause?

And the idea that this would be user customizable is scary indeed. Why would the user need a 1000 fields to customize? Most applications I've seen which give the user a chance to customize some fields set a small limit (usually less than 10). If there is that much they need to customize, then the application hasn't done a good job of defining what the customer actually needs.

Sometimes as a developer you just have to stand up and say no, this is a bad idea. This is one of those times.

As to what you shoud do instead (other than normalize), I think we would need more information to point you in the right direction.

And BTW, float is an inexact datatype and should not be used for fields where calculations are taking place unless you like incorrect results.

Answer 6

This smells like a bad design to me.

Things to consider:

Will most of those columns be contain NULL values?

Will many be named Property001, Property002, Property003, etc...?

If so, I recommend you rethink your data normalization.

Answer 7

from SQL2005 documentation:

SQL Server 2005 can have up to two billion tables per database and 1,024 columns per table. (...) The maximum number of bytes per row is 8,060. This restriction is relaxed for tables with varchar, nvarchar, varbinary, or sql_variant columns that cause the total defined table width to exceed 8,060 bytes. The lengths of each one of these columns must still fall within the limit of 8,000 bytes, but their combined widths may exceed the 8,060 byte limit in a table.

what is the functionality of these columns? why not better split them into master table, properties (lookup tables) and values?

Answer 8

That is too many. Any more than 50 columns wide and you are asking for trouble in performance, code maintenance, and troubleshooting when there are problems.

Answer 9

Seems like an awful lot. I would first make sure that the data is normalized. That might be part of your problem. What type of purpose will this data serve? Is it for reports? Will the data change?

I would think a table that wide would be a nightmare performance and maintenance-wise.

Answer 10

As a rule: the wider the table the slower the performance. Many thin tables are preferable to one fat mess of a table.

If your table is that wide it's almost certainly a design issue. There's no real rule on how many is preferable, I've never really come across tables with more than 20 columns in the real world. Just group by relation. It's a RDBMS after all.