Table with lots of columns

Question

If my table has a huge number of columns (more than 80), should I split it into several tables with a 1 to 1 ratio or just keep it as it is? What for? My main concern is performance.

PS - my table is already in 3rd normal form.

PS2 - I am using MS Sql Server 2008.

PS3 - I don’t need to access all the tabular data right away, but you have 3 different categories of data in this table that I refer to separately. This is something like: member preferences, member account, member profile.

Answer 1

80 columns are really not many ...

I would not worry about this in terms of performance. Having a separate table (if you usually use all the data in standard operations) is likely to exceed several tables with 1-1 relationships, especially if you index accordingly.

I would be worried about this (potentially) in terms of service. The more columns of data in one table, the less clear is the role of this table in your great schema. Also, if you usually only use a small subset of the data, and all 80 columns are not always required, partitioning into 2+ tables can help performance.

Answer 2

Go to the question of performance - it depends. The larger the line, the fewer lines can be read from disk in one read. If you have many rows and you want to read the main information from the table very quickly, it may be worth splitting it into two tables: one with small rows containing only the main information that can be quickly read, and an additional table containing all the information that you rarely use which you can look for when necessary.

Answer 3

On the other hand, from the point of view of maintenance and testing, if, as you say, you have 3 different groups of data in the same table, although they all have the same unique identifier (for example, member_id), it may make sense to split it into separate tables.

If you need to add fields to tell your profile information section in the member details table, do you really want the risk of re-testing the parameters and elements of your application’s account to prevent knocking on beats.

Also for audit purposes, if you want to track the last user ID / timestamp to change member data. If the admin application allows you to update the Preferences / Account Details / Profile Details settings separately, then it makes sense to have them in separate tables to make it easier to keep track of updates.

Not exactly the SQL / Performance answer, but maybe something that can be viewed from the DB project and App pov

Answer 4

Depends on these columns. If you have hard-coded duplicate fields, such as Colour1, Colour2, Colour3, then they are candidates for child tables. My general rule is that if there is more than one field of the same type (Color), then you can also specify the code for N, and not a fixed number.

Rob.

Answer 5

1-1 could be simpler if you said Member_Info; Member_Pref; Member_Profile Too many columns can make it work if you want a lot of varchar (255) as you can go over the row size limit and that just makes it too confusing.

Just make sure you have the correct forgein key constraints, and so there is always one row in each table with the same member_id