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

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)
);

---

My former colleague also wrote a blog about this:

Understanding the maximum number of columns in a MySQL table

The answer is not so straightforward as you might think.