Menu
If my table has a huge number of columns (over 80) should I split it into several tables with a 1-to-1 relationship or just keep it as it is? Why? My main concern is performance.
PS - my table is already in 3rd normal form.
PS2 - I am using MS Sql Server 2008.
PS3 - I do not need to access all table data at once, but rather have 3 different categories of data within that table, which I access separately. It is something like: member preferences, member account, member profile.
951
A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
Column Count Limits MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table.
Answer. For the columns in a table, there is a maximum limit of 1024 columns in a table.
Which one is more efficient in terms of performance and why? More rows. That is what relational databases are designed for. Multiple tables with the same structure are usually a sign of a bad design.
80 columns really isn't that many...
I wouldn't worry about it from a performance standpoint. Having a single table (if you're typically using all of the data in your standard operations) will probably outperform multiple tables with 1-1 relationships, especially if you're indexing appropriately.
I would worry about this (potentially) from a maintenance standpoint, though. The more columns of data in a single table, the less understandable the role of that table in your grand scheme becomes. Also, if you're typically only using a small subset of the data, and all 80 columns are not always required, splitting into 2+ tables might help performance.
59
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
