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