SQL Server – Adding New Boolean Column vs. Creating Related 0-1 Table

database-designsql server

I have a big table (200 billion rows) like this :

CREATE TABLE A (
A_Id INT NOT NULL PRIMARY KEY, 
A_Attribute1 VARCHAR(100) NOT NULL, 
... )

I must add new boolean column NOT NULL with false as a default value, like this :

ALTER TABLE A ADD A_BitAttribute BIT NOT NULL DEFAULT 0

Moreover, in the future, this column will be 0 most of time.

To prevent the cost of adding this column in the A table, I thinked about creating a new related table, like this :

CREATE TABLE ABitAttribute (
A_Id INT NOT NULL PRIMARY KEY, 
, CONSTRAINT FK_ABitAttribute_A FOREIGN KEY (A_Id) REFERENCES A (A_Id) ON DELETE CASCADE
)

A row in this table indicates True for A_Id, no line indicates False. The outer join needed to retrieve the data doesn't bother me too much.

Are there any major drawback or theory violation in using this kind of technique ?

edit :

  • it has to be SQL Server 2005 compatible
  • the interruption of service has to be minimal (we usually garanty that database has no more than 1 hour)

Best Answer

From a theoretical point of view, booleans are a bit suspicious (not necessarily wrong though). The idea is that we store true propositions, the absence of something is considered false. So, it's definitely nothing wrong with your suggestion from a theoretical viewpoint. Without knowing what your attribute represents it's difficult to be more specific than that.

From a practical point of view, since most of the rows are false, you will save some space by not storing false for almost all rows.

The downside is that you will have to do an outer join, which may affect performance.

EDIT: As pointed out by @eckes in his comment, deletes in A will have to investigate ABitAttribute for presence of the id beeing deleted. The same goes for updates (if the id column being updated). For inserts, there should be no affect

Related Question