SQL – Pro e contro delle colonne bitmask

21 nov 2014 22:47

_Logo Un breve post per mostrare come gestire colonne di tipo bitmask e utilizzare gli operatori bitwise di SQL. Le colonne bitmask sono spesso comode quando i possibili flag non sono predefiniti e possono variare nel tempo: altrimenti si tende a preferire singole colonne bit. Meglio ancora una tabella di supporto.

Per memorizzare una colonna bitmask si utilizza una colonna di tipo intero (int, smallint, tinyint) o binario (binay o varbinary). Ecco una classica query in cui nell’anagrafica utenti vi è una colonna ruoli:

SELECT * FROM Users WHERE Roles & 6 != 0

Un pratico esempio, estratto da StackOverflow, è il seguente:

WITH test (id, username, roles)
AS
 (
    SELECT 1,'Dave',6
    UNION SELECT 2,'Charlie',2
    UNION SELECT 3,'Susan',4
    UNION SELECT 4,'Nick',1
)
SELECT * FROM test WHERE (roles & 6) != 0

Questi gli operatori disponibili:

  • & - bitwise AND
  • | - bitwise OR
  • ^ - bitwise Exlusive OR
  • ~ - bitwise NOT

Vi sono diversi aspetti negativi da considerare nell’utilizzare colonne bitmask:

  • non sono chiare e necessitano di specifica documentazione (per la manutenzione),
  • non rispettano i normali design pattern dei database,
  • non sono indicizzabili (o meglio i singoli valori non sono indicizzabili se non come calcolate),
  • se si eseguono più confronti nella stessa query l’indice sulla colonna deve essere riattraversato più volte,
  • non fanno guadagnare tanto spazio visto che SQL Server comprime più colonne bit in un singolo byte.

Si tende quindi a preferire un insieme di colonne bit, generalmente più comprensibili e soprattutto indicizzabili. Ma se i possibili valori sono tanti e variabili nel tempo l’alternativa migliore è quella di utilizzare una tabella di supporto: ad esempio, una tabella UserRoles per salvare i ruoli di ogni utente. In questo caso si rispettano i principi dei database relazionali e se ne sfrutta appieno la potenza.

Tag: SQL Server