SQL Optimization: how many columns on a table?

Question

In a recent project I have seen a tables from 50 to 126 columns.

Should a table hold less columns per table or is it better to separate them out into a new table and use relationships? What are the pros and cons?

Answer 1

Generally it's better to design your tables first to model the data requirements and to satisfy rules of normalization. Then worry about optimizations like how many pages it takes to store a row, etc.

I agree with other posters here that the large number of columns is a potential red flag that your table is not properly normalized. But it might be fine in this case. We can't tell from your description.

In any case, splitting the table up just because the large number of columns makes you uneasy is not the right remedy. Is this really causing any defects or performance bottleneck? You need to measure to be sure, not suppose.

Answer 2

A good rule of thumb that I've found is simply whether or not a table is growing rows as a project continues,

For instance:

On a project I'm working on, the original designers decided to include site permissions as columns in the user table.

So now, we are constantly adding more columns as new features are implemented on the site. obviously this is not optimal. A better solution would be to have a table containing permissions and a join table between users and permissions to assign them.

However, for other more archival information, or tables that simply don't have to grow or need to be cached/minimize pages/can be filtered effectively, having a large table doesn't hurt too much as long as it doesn't hamper maintenance of the project.

At least that is my opinion.

Answer 3

Usually excess columns points to improper normalization, but it is hard to judge without having some more details about your requirements.

Answer 4

I can picture times when it might be necessary to have this many, or more columns. Examples would be if you had to denormalize and cache data - or for a type of row with many attributes. I think the keys are to avoid select * and make sure you are indexing the right columns and composites.

Answer 5

If you had an object detailing the data in the database, would you have a single object with 120 fields, or would you be looking through the data to extract data that is logically distinguishable? You can inline Address data with Customer data, but it makes sense to remove it and put it into an Addresses table, even if it keeps a 1:1 mapping with the Person.

Down the line you might need to have a record of their previous address, and by splitting it out you've removed one major problem refactoring your system.

Are any of the fields duplicated over multiple rows? I.e., are the customer's details replicated, one per invoice? In which case there should be one customer entry in the Customers table, and n entries in the Invoices table.

One place where you need to not fix broken normalisation is where you have a facts table (for auditing, etc) where the purpose is to aggregate data to run analyses on. These tables are usually populated from the properly normalised tables however (overnight for example).

Answer 6

It sounds like you have potential normalization issues.

If you really want to, you can create a new table for each of those columns (a little extreme) or group of related columns, and join it on the ID of each record.

Answer 7

It could certainly affect performance if people are running around with a lot of "Select * from GiantTableWithManyColumns"...

Answer 8

Here are the official statistics for SQL Server 2005 http://msdn.microsoft.com/en-us/library/ms143432.aspx

Keep in mind these are the maximums, and are not necessarily the best for usability.

Think about splitting the 126 columns into sections. For instance, if it is some sort of "person" table you could have

Person ID, AddressNum, AddressSt, AptNo, Province, Country, PostalCode, Telephone, CellPhone, Fax

But you could separate that into Person ID, AddressID, PhoneID

Address ID, AddressNum, AddressSt, AptNo, Province, Country, PostalCode

Phone ID, Telephone, Cellphone, fax

In the second one, you could also save yourself from data replication by having all the people with the same address have the same addressId instead of copying the same text over and over.