6

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)
Improve this question
2
  • I would expect additional attributes in the future and therefore add the boolean as an additional row. You need to be aware of the additional FK lookups on delete (I am not sure about insert/update) – eckes Jul 30 '19 at 9:37
  • Creating a new table sounds like the “Closed World Interpretation” as discussed here. – MDCCL Jul 30 '19 at 19:23
4

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

Improve this answer
4
  • 1
    It may also be worth adding that adding a new column with a default non-null value is a very fast operation but only in recent versions/editions of SQL Server. – ypercubeᵀᴹ Jul 30 '19 at 10:10
  • 2
    > Starting with SQL Server 2012 (11.x) Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. – ypercubeᵀᴹ Jul 30 '19 at 10:22
  • 1
    But if you are on an older version (2008R2 or older) or in a non-Enterprise edition, adding a not-null column on a 200 billion rows table will certainly take some time (to rebuild the table). – ypercubeᵀᴹ Jul 30 '19 at 10:24
  • 1
    Sadly it is targeting databases non-Enterprise editions and SQL Server 2005 :( – Masure Jul 31 '19 at 10:16
3

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

You should try not to change the logical database design because of storage cost, or operational costs of modifying tables. This is not always possible, but you should try.

So the default choice here should be to add the column. You might want to store this large table as a Clustered Columnstore, which provide column-wise storage, and excellent compression of low-cardinality columns.

Improve this answer
1
  • Clustered Columnstore is an interesting feature but I have to stick with SQL Server 2005 compatibility I would rather have done the new column but database availability is very time constrained – Masure Jul 31 '19 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.