Como implementar um atributo cancel em uma tabela?

Não tenho certeza se gosto NULLpor falta de cancelamento. Acho que prefiro ter um sinalizador Active que seja 1 por padrão e definido como 0 quando algo for cancelado. Agora suas verificações são simplesmente WHERE active = 1 ou WHERE active = 0, em vez de lidar com todas as verificações OR IS NULLou OR IS NOT NULL, e uma linha pode estar inativa por outros motivos além de "cancelada" caso esse modelo amadureça por outros motivos. Você pode usar índices filtrados para tornar as consultas de um tipo ou outro um pouco mais eficientes se estiver usando o SQL Server 2008+. NULLimplica desconhecido e, no caso de você ter apenas dois estados, "não cancelado" não é desconhecido.

Quanto a quem, por que, quando? Talvez seja uma tabela completamente separada e possa funcionar como um log de auditoria (incluindo se as entidades forem marcadas como canceladas, depois tornadas ativas novamente, depois canceladas novamente, etc.). Seria apenas algo como:

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,
    when DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    why NVARCHAR(MAX)
);

Você tem 97% de linhas ativas e 95% de consultas nesses 97%.

Uma cláusula WHERE que seleciona 97% das linhas não será ajudada pela indexação: não é seletiva o suficiente

Eu consideraria 2 tabelas para suas "Coisas": ThingActive e ThingCancelled

Para os 5% em que você consulta linhas canceladas, você lê apenas a tabela Cancelada ou um UNION/View

Uma reviravolta nisso...

A tabela cancelada armazena apenas o ID da tabela principal e as colunas extras (portanto, NÃO EXISTE para encontrar a ativa).

Em cerca de 95% das consultas, desejo selecionar apenas as linhas não canceladas.

Isso provavelmente não importa se "menos de 3% das linhas forem canceladas". - deixá-los ser digitalizados de qualquer maneira?

Oráculo:

Para a restrição exclusiva, se você incluir cancelled_seq(de uma sequência) no índice, as linhas canceladas nunca farão com que a restrição falhe

Quais são os prós e contras de usar NULL em alguma coluna como indicador de não cancelamento?

profissional: você pode procurar as linhas canceladas rapidamente se indexar essa coluna, pois as linhas com todos os nulls serão omitidas do índice

contra: obscuridade

Realmente depende dos dados de que estamos falando.

No entanto, eu pessoalmente não gosto de usar um NULL para indicar um status (a menos que seja uma chave estrangeira ou algo desse tipo). Prefiro um número inteiro com o padrão zero, usando zero como forma de indicar que o não foi cancelado.

Meu raciocínio: se você decidir adicionar outro "status" posteriormente, terá que implementar uma lógica complicada, em vez de apenas incrementar o inteiro.

Outra opção é criar uma segunda tabela (quase duplicada) com essas linhas extras. Quando a for cancelado, mova-o para a tabela "cancelled_". Claro, isso presumindo que sua tabela original tinha poucas colunas. Caso contrário, meus instintos de normalização começam a entrar em ação.

Como alternativa, você pode criar uma tabela de "cancelamento" que contém os dados cancelados com referências de chave estrangeira de volta à tabela principal. Mas, novamente, depende totalmente dos seus dados e do seu ambiente.