Maximum number of columns in a table

Question

Problem1: What is the maximum no of columns we can have in a table

Problem2: What is the maximum no of columns we should have in a table

Answer 1

1) http://msdn.microsoft.com/en-us/library/aa933149%28SQL.80%29.aspx

1024 seems to be the limit.

2) Much less than 1024 :). Seriously, it depends on how normalized you want your DB to be. Generally, the fewer columns you have in a table the easier it will be for someone to understand (normally). Like for a person table, you might want to store the person's address in another table (person_address, for example). It's best to break your data up into entities that make sense for your business model, and go from there.

Answer 2

1. I think 4096 in mysql, SQL Server I don't know
2. I asked the same question a few months ago in a special scenario, maybe the answers help you decide. Usually, as few as possible I would say.

Answer 3

Answer 1: Probably more than you have, but not more than you will grow to have.

Answer 2: Fewer than you have.

Asking these questions usually indicates that you haven't designed the table well. You probably are practicing the Metadata Tribbles antipattern. The columns tend to accumulate over time, creating an unbounded set of columns that store basically the same type of data. E.g. subtotal1, subtotal2, subtotal3, etc.

Instead, I'm guessing you should create an additional dependent table, so your many columns become many rows. This is part of designing a proper normalized database.

CREATE TABLE Subtotals (
  entity_id    INT NOT NULL,
  year_quarter SMALLINT NOT NULL, -- e.g. 20094
  subtotal NUMERIC(9,2) NOT NULL,
  PRIMARY KEY (entity_id, year_quarter),
  FOREIGN KEY (entity_id) REFERENCES Entities (entity_id)
);

Answer 4

SQL 2000 : 1024

SQL 2005 : 1024

SQL 2008 : 1024 for a non-wide table, 30k for a wide table.

The wide tables are for when you have used the new sparse column feature in SQL 2008 which is designed for when you have a large number of columns that are normally empty.

Just because these limits are available, does not mean you should be using them however, I would start with designing the tables based on the requirements and then check whether a vertical partitioning of 1 table into 2 smaller tables is required etc.

Answer 5

2) There are plenty of guidelines out there. In particular regarding database normalization. The overarching principle is always to be able to adapt. Similar to classes, tables with large number of columns are not very flexible. Some of the questions you should ask yourself:

• Does Column A describes an attribute of the Object (Table) that could/should be grouped with Column B
• Data update. Keep in mind that most RDBMS perform a row lock when updating values. This means that if you are constantly updating Column A while another process is updating Column B, they will fight for the row and this will create contention.

Database design is an art more than a science. While guidelines and technical limitations will get you in the right direction, there are no hard rules that will make your system work or fail 100%.