Which is the best way to implement a canceled attribute in a database table, provided that less than say 3 % of the rows are canceled.

For canceled data I want to store the additional information

  • by whom
  • when
  • why

In about 95 % of the queries I only want to select the non canceled rows.

Further I want to be able implement unique constraints to some columns for the not canceled columns.

What are the pros and cons of using NULL in some column as indicator for not canceled ?

4 Answers 4


You have 97% active rows and 95% of queries on this 97%.

A WHERE clause that selects 97% of rows won't be helped by indexing: it isn't selective enough

I'd consider 2 tables for your "Things": ThingActive and ThingCancelled

For the 5% where you query cancelled rows, you read either the Cancelled table only or a UNION/View

A twist on this...

The cancelled table stores only main table ID and the extra columns (so it's NOT EXISTS to find active).


I'm not sure I like NULL for lack of being cancelled. I think I'd rather have an Active flag that is 1 by default, and set to 0 when something is cancelled. Now your checks are simply WHERE active = 1 or WHERE active = 0, instead of dealing with all the OR IS NULL or OR IS NOT NULL checks, and a row can be inactive for other reasons other than "canceled" should that model ever mature into other reasons. You can use filtered indexes to make the queries of one type or the other slightly more efficient if you are using SQL Server 2008+. NULL implies unknown, and in the case where you only have two states, "not cancelled" is not unknown.

As for the who, why, when? Perhaps that is a separate table altogether, and can act like an audit log (including if entities are marked as cancelled, then made active again, then cancelled again, etc). It would just be something like:

CREATE TABLE dbo.<something>AuditLog
    key INT NOT NULL FOREIGN KEY REFERENCES dbo.<something>(key),
    action NVARCHAR(32) NOT NULL, 
      -- perhaps a CHECK here or a foreign key to an 
      -- action code table, e.g. 'made active, made inactive'
    who NVARCHAR(64) NOT NULL,

In about 95 % of the queries I only want to select the non canceled rows.

This probably doesn't matter if "less than 3% of the rows are canceled." - let them be scanned anyway?


For the unique constraint, if you include cancelled_seq (from a sequence) in the index then cancelled rows will never cause the constraint to fail

What are the pros and cons of using NULL in some column as indicator for not cancelled?

pro: you can scan for the cancelled rows quickly if you index that column as rows with all nulls will be omitted from the index

con: obscurity


It really, really depends on the data we're talking about.

However, I personally don't like using a NULL to indicate a status (unless it's a foreign key or something of that sort). I prefer an integer with a default of zero, using zero as the way to indicate that the hasn't been cancelled.

My reasoning: If you decide to add another "status" later, you will have to implement complicated logic, instead of just incrementing the integer.

Another option is that you could create a second (almost duplicate) table that has those extra rows. When a is cancelled, move it off into that "cancelled_" table. Of course, that's presuming your original table had very few columns. Otherwise, my normalization instincts start kicking in.

Alternatively, you could create a "cancellation" table that holds the cancelled data with foreign key references back to the main table. But again, it totally depends on your data and your environment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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