Menu
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.
706
However, Sparse Columns and the wide-table feature in SQL Server are not support with P8 CPE at this time. One possible way to avoid the 1024 maximum number of columns is to create multiple object stores for different classes of objects, for different business needs.
In SQL Server you can include non-key columns in a nonclustered index, to avoid the limitation of a maximum of 32 key columns. For more information, see Create Indexes with Included Columns. Tables that include sparse column sets include up to 30,000 columns.
A common problem with SQL is the high probability of having too many columns in the projection. This may be due to reckless usage of SELECT * or some refactoring which removes the need to select some of the projected columns, but the query was not adapted.
There is no precise guidance. A table could be as little as one column or as many as the max, 1024. However, in general, you'll probably see no more than 10-15 columns in a table in a well normalized database.
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.
192
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?
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
