Unicità condizionata, NULL e ANSI_NULLS: Soluzioni T-SQL senza trigger

Recentemente mi sono trovato ad affrontare un caso interessante relativo alla gestione di un vincolo UNIQUE con alcune particolarità su una tabella di un database SQL Server. La richiesta è stata quella controllare che i valori inseriti o modificati in una colonna siano unici, permettendo però NULL multipli.

Consideriamo, a titolo di esempio semplificato, lo schema della tabella dbo.EmailTable. La tabella memorizza alcuni attributi di una anagrafica contatti tra cui il Nome, il Cognome e l’Email. L’attributo Email non è obbligatorio ma quando presente deve essere univoco. L’assenza dell’attributo viene gestita con un NULL.

CREATE TABLE dbo.EmailTable
(
  ID INTEGER IDENTITY(1, 1) NOT NULL
  ,FirstName VARCHAR(32) NOT NULL
  ,LastName VARCHAR(32) NOT NULL
  ,Email VARCHAR(64) NULL
);
GO

L’implementazione di un UNIQUE filtered-index sulla colonna Email è la soluzione migliore, garantisce l’univocità, e la possibilità di specificare una clausola WHERE permette di soddisfare la specifica che permette NULL multipli. Applicato alla tabella di esempio dbo.EmailTable permetterà di controllare i valori della colonna Email non nulli, garantendone l’univocità. La creazione dell’indice può essere eseguita con il seguente comando T-SQL.

CREATE UNIQUE NONCLUSTERED INDEX IX_UQ_EmailTable_Email ON dbo.EmailTable
(
  [Email]
)
WHERE
  (Email IS NOT NULL);
GO

La creazione dell’indice viene eseguita correttamente ma è necessario precisare che l’indice UNIQUE filtrato e la tabella sottostante sono stati creati con l’opzione ANSI_NULLS impostata a ON. Non sarebbe stato possibile creare l’indice con l’opzione ANSI_NULLS impostata a OFF, se avessimo tentato di farlo avremmo ricevuto il seguente messaggio di errore.

Msg 1934, Level 16, State 1, Line 23

CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

L’errore evidenzia l’impossibilità di creare l’indice a causa dell’impostazione a OFF di ANSI_NULLS. Questa opzione controlla il comportamento degli operatori di confronto (“=”, “<>”, “>=”, etc.) quando si trovano a trattare un NULL.

Procediamo con alcune prove in ambiente di test utilizzando l’applicazione che dovrà gestire l’anagrafica contatti, inseriamo alcuni contatti provvisti di Email e altri che ne sono sprovvisti con l’obiettivo di testare la soluzione. L’applicazione utilizza una connessione con l’opzione ANSI_NULLS impostata a OFF e al momento la scelta non è negoziabile.

INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Crystie', 'Tibald', NULL);

INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Spencer', 'Bras', 'sbras1@sfgate.com');

INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Kaiser', 'Bachellier', NULL);
GO

Tutti i comandi di INSERT restituiscono lo stesso errore:

Msg 1934, Level 16, State 1, Line 29

INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Per evitare l’errore, anche la connessione applicativa deve impostare a ON ANSI_NULLS, purtroppo però non è una modifica negoziabile ed è necessario trovare una soluzione alternativa.

Ripartiamo dai pre-requisiti: controllare che i valori inseriti o modificati in una colonna siano unici, permettendo NULL multipli e con l’opzione ANSI_NULLS impostata a OFF sulla connessione applicativa che esegue i comandi DML.

Ci orientiamo verso una soluzione basata su CHECK CONSTRAINT con la condizione di check verificata attraverso una funzione scalare in grado di:

• Verificare che il valore passato in ingresso sia unico
• Accettare NULL multipli, la mancanza del valore (NULL) è sempre accettata
• Lavorare correttamente con l’opzione ANSI_NULLS impostata a OFF

La funzione scalare potrà essere utilizzata nella condizione di verifica del vincolo check. Il seguente script T-SQL crea la funzione dbo.fn_EmailIsUniqueOrNull.

CREATE OR ALTER FUNCTION dbo.fn_EmailIsUniqueOrNull(@Email VARCHAR(64) = '')
RETURNS BIT
AS
BEGIN
  IF @Email IS NULL
    RETURN 1;

  IF (
      SELECT COUNT(*) 
      FROM dbo.EmailTable 
      WHERE Email = @Email
     ) <= 1
    RETURN 1;
  
  RETURN 0;
END;
GO

La funzione può essere usata in un CHECK CONSTRAINT perché è deterministica, non accede a oggetti esterni diversi dalla tabella stessa, e lavora correttamente con ANSI_NULLS OFF. La logica (COUNT <= 1) considera il fatto che i CHECK CONSTRAINT vengono verificato dopo l’inserimento dei dati e il record con l’Email da verificare è già all’interno della tabella nel momento della verifica. Il seguente piano di esecuzione è relativo ad un inserimento nella tabella dbo.EmailTable con il vincolo check creato, si osserva che l’operatore Assert esegue il controllo di coerenza dell’integrità di dominio dopo l’operatore Table Insert.

Al momento la funzione non effettua controlli sulla formattazione dell’Email e neppure sulla possibilità che sia vuota ma si possono implementare dopo aver effettuato un primo test.

La soluzione migliore è di gran lunga quella che prevede l’indice filtrato. Avere una user-defined function nella definizione di un CHECK CONSTRAINT può rallentare gli inserimenti massivi, perché la funzione verrà chiamata una volta per ogni riga (l’inlining di UDF scalari, a partire da SQL Server 2019, migliora questo aspetto ma in questa situazione l’inlining non può avviene). Considerato che la tabella in questione non riceve e non riceverà inserimenti massivi, il compromesso sulle performance può essere accettato. Il CHECK CONSTRAINT sulla tabella dbo.EmailTable può quindi essere creato con il seguente comando.

ALTER TABLE dbo.EmailTable ADD CONSTRAINT CK_EmailTable_Email CHECK
(
  dbo.fn_EmailIsUniqueOrNull(Email) = 1
);
GO

Il vincolo check può essere testato con i seguenti comandi di inserimento e modifica.

INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Crystie', 'Tibald', NULL);

INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Spencer', 'Bras', 'sbras1@sfgate.com');

INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Kaiser', 'Bachellier', NULL);

INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Farrell', 'Nehlsen', 'fnehlsen3@slate.com');

UPDATE dbo.EmailTable SET Email = 'kallmann91@jigsy.com' WHERE Email = 'kallmann9@jigsy.com';

UPDATE dbo.EmailTable SET Email = NULL WHERE Email = 'jmoorrud6@apache.org';

INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Kailey', 'Allmann', 'kallmann91@jigsy.com');

Conclusioni

Gestire l’unicità condizionata in presenza di valori NULL su SQL Server può sembrare banale, ma in ambienti dove l’opzione ANSI_NULLS non può essere impostata a ON, le soluzioni basate su indici filtrati non sono percorribili. In questi scenari, l’utilizzo di una funzione scalare deterministica all’interno di un CHECK CONSTRAINT rappresenta una valida alternativa, che permette di rispettare i vincoli richiesti senza ricorrere a trigger o logiche più invasive. Sebbene questa soluzione possa introdurre un overhead in fase di inserimento o modifica, risulta efficace e sicura in tutti quei contesti dove il volume dei dati non è elevato. Come sempre, è importante bilanciare correttezza dei dati e prestazioni, valutando attentamente le caratteristiche specifiche dell’applicazione.