Torna a: SQL Server Tutorial per principianti e professionisti
SQL Server Unique Index
In questo articolo, discuterò SQL Server Unique Index con esempi. Quando creiamo un indice SQL Server usando l’opzione Unique, allora si chiama SQL Server Unique Index. Quindi la colonna (o le colonne) su cui viene creato l’indice unico non permetterà valori duplicati, cioè funziona come un vincolo unico.
Il SQL Server Unique Index garantisce che la colonna su cui viene creato l’indice non accetterà valori duplicati. Si prega di leggere i seguenti articoli che sono necessari per capire il SQL Server Unique Index.
Indici SQL Server
Indice clusterizzato in SQL Server
Indici non clusterizzati in SQL Server
Comprendiamo SQL Server Unique Index con un esempio.
Useremo la seguente tabella Employees. Usate il seguente script SQL per creare e popolare la tabella Employees con i dati di prova.
Dato che abbiamo segnato la colonna Id come colonna chiave primaria nella tabella Employees, per default viene creato un indice unico clustered nella colonna Id con Id come chiave di indice. Per controllare se un indice clustered unico è stato creato o meno, utilizzate la stored procedure di sistema sp_helpindex come mostrato di seguito.
sp_helpindex Employees
Dal diagramma sopra, si vede chiaramente che un indice clustered unico viene creato sulla colonna Id della tabella Employees. Dato che abbiamo un UNIQUE CLUSTERED INDEX sulla colonna Id della tabella Employees, qualsiasi tentativo di duplicare i valori chiave, cioè i valori della colonna Id della tabella Employee, darà un errore.
Provo a inserire due record con gli stessi valori Id come mostrato di seguito.
INSERT INTO Employees VALUES(1,’Pranaya’, ‘Rout’,4500,’Maschio’,’Mumbai’)
INSERT INTO Employees VALUES(1,’Anurag’, ‘Mohanty’,2500,’Maschio’,’Delhi’)
Quando cerchiamo di inserire i due record di cui sopra, ci dà il seguente errore che assicura che non possiamo inserire valori chiave duplicati quando c’è un indice unico in SQL Server.
‘Violazione del vincolo PRIMARY KEY ‘PK__Employee__3214EC0775FF9526’. Impossibile inserire una chiave duplicata nell’oggetto ‘dbo.Employees’. Il valore della chiave duplicata è (1)’.
Ora proviamo ad eliminare l’indice unico clusterizzato sulla colonna Id utilizzando il comando DROP come mostrato di seguito.
DROP INDEX Employees.PK__Employee__3214EC0775FF9526
Quando cerchiamo di eseguire il comando DROP INDEX di cui sopra, ci dà il seguente errore
‘Un DROP INDEX esplicito non è permesso sull’indice ‘Employees.PK__Employee__3214EC0775FF9526′. Viene utilizzato per l’applicazione del vincolo PRIMARY KEY.’
Quindi il messaggio di errore di cui sopra dimostra che SQL server utilizza internamente l’indice UNIQUE per applicare l’unicità dei valori e della chiave primaria.
Per vedere la chiave primaria e l’indice, espandete la cartella keys nella finestra object explorer, e potrete vedere il vincolo di chiave primaria. Allo stesso modo, espandete la cartella degli indici nella finestra di object explorer e potrete vedere l’indice unico clustered come mostrato nel diagramma sottostante.
Come potete vedere nell’object explorer mostra solo la parola clustered. Per confermare se si tratta di un indice UNICO o meno, cliccate con il tasto destro sull’indice e selezionate proprietà. La finestra delle proprietà mostra la casella di controllo UNIQUE selezionata come mostrato nel diagramma sottostante.
Cancella indice cluster
Non possiamo eliminare l’indice unico clusterizzato usando il comando DROP ma SQL Server ci permette di eliminare l’UNIQUE CLUSTERED INDEX dall’object explorer. Quindi clicchiamo con il tasto destro del mouse sull’indice, quindi selezioniamo l’opzione DELETE e infine clicchiamo su OK. Potete vedere che insieme all’indice UNIQUE, anche il vincolo di chiave primaria viene eliminato.
Ora, proviamo a inserire alcuni valori duplicati per la colonna ID dei dipendenti e potete vedere che le righe vengono inserite nella tabella senza alcun errore di violazione della chiave primaria.
INSERT INTO Employees VALUES(2,’Priyanka’, ‘Dwegaan’,4500,’Female’,’Mumbai’)
INSERT INTO Employees VALUES(2,’Preety’, ‘Tiwary’,2500,’FEmale’,’Delhi’)
Una volta inseriti i due record di cui sopra, verificare la tabella Employee come mostrato di seguito.
SELECT * FROM Employees
Output:
Quindi questo dimostra che l’indice UNIQUE in SQL Server è usato per far rispettare l’unicità dei valori e il vincolo di chiave primaria.
L’UNIQUITÀ è una proprietà di un indice in SQL Server e sia gli indici CLUSTERED che NON-CLUSTERED possono essere creati come UNIQUE.
NOTE:
Non possiamo creare un indice unico su una singola colonna se questa colonna contiene NULL in più di una riga. Allo stesso modo, non possiamo creare un indice unico su più colonne se la combinazione di colonne contiene NULL in più di una riga. Questi sono trattati come valori duplicati ai fini dell’indicizzazione.
Creiamo un indice unico non clustered sulle colonne FirstName e LastName della tabella Employees
CREATE UNIQUE NONCLUSTERED INDEX UIX_Employees_FastNamee_LastName On Employees(FastName, LastName)
L’indice unico non clusterizzato di cui sopra assicura che nessun 2 voci nell’indice abbiano gli stessi nomi veloci e cognomi.
Nell’articolo sul Vincolo Unico, abbiamo già discusso che un Vincolo Unico in SQL Server può essere usato per far rispettare l’unicità dei valori, attraverso una o più colonne.
NOTA: Il vincolo Unico in SQL Server può essere sia unico clustered che unico non clustered. Durante la creazione di un indice in SQL Server, se clustered o non-clustered non è specificato, di default è non-clustered.
Quali sono le differenze tra vincoli UNIQUE e indici UNIQUE?
Non ci sono grandi differenze tra un vincolo unico e un indice unico in SQL Server. Infatti, quando aggiungiamo un vincolo unico, un indice unico viene creato dietro le quinte.
Per capire questo, aggiungiamo un vincolo unico sulla colonna City della tabella Employees
ALTER TABLE Employees ADD CONSTRAINT UQ_Emplyees_City UNIQUE (City)
Ora quando eseguiamo la query di cui sopra ci dà il seguente errore.
L’istruzione CREATE UNIQUE INDEX è terminata perché è stata trovata una chiave duplicata per il nome dell’oggetto ‘dbo.Employees’ e il nome dell’indice ‘UQ_Emplyees_City’. Il valore della chiave duplicata è (Mumbai).
Questo perché abbiamo già dei valori duplicati sulla colonna City della tabella Employees e quindi non permette di creare un indice unico sulla colonna City.
Perciò prima tronchiamo la tabella e poi creiamo l’indice come mostrato di seguito.
TRUNCATE TABLE Employees
ALTER TABLE Employees ADD CONSTRAINT UQ_Emplyees_City UNIQUE (City)
Ora, la dichiarazione viene eseguita con successo.
A questo punto, ci si può aspettare che venga creato un vincolo unico nella cartella dei vincoli. Aggiornate ed espandete la cartella constraints nella finestra di object explorer e potrete vedere che il vincolo non è presente in questa cartella. Ora, aggiornate ed espandete la cartella ‘indexes’ e nella cartella indexes, potete vedere un indice UNICO NON-CLUSTERED con il nome UQ_Employees_City come mostrato sotto.
Potete anche verificare i vincoli disponibili di una tabella utilizzando la stored procedure di sistema SP_HELPCONSTRAINT come mostrato di seguito
EXECUTE SP_HELPCONSTRAINT Employees
Ci darà il seguente risultato.
Quando creiamo un UNIQUE Constraint in SQL Server, esso crea effettivamente un indice UNIQUE dietro la scena. Quindi un indice UNIQUE in SQL Server può essere creato esplicitamente utilizzando l’istruzione CREATE INDEX o indirettamente utilizzando un vincolo UNIQUE.
Quando si dovrebbe creare un vincolo unico su un indice unico?
Per rendere chiare le intenzioni creare un vincolo unico quando l’obiettivo è l’integrità dei dati. Questo rende molto chiaro l’obiettivo dell’indice. In entrambi i casi, i dati sono convalidati allo stesso modo, e l’ottimizzatore di query non fa differenza tra un indice unico creato da un vincolo unico o creato manualmente.
NOTE:
Quando creiamo un vincolo PRIMARY KEY in SQL Server, un indice unico clustered sulla colonna o sulle colonne viene creato automaticamente se non esiste già un indice clustered sulla tabella e non si specifica un indice unico non clustered.
Quando creiamo un vincolo UNIQUE in SQL Server, un indice unico non clusterato viene creato automaticamente per imporre un vincolo UNIQUE per default. È possibile specificare un indice unico clustered se un indice clustered sulla tabella non esiste già.
Non possiamo creare un vincolo UNIQUE o un indice UNIQUE in SQL Server su una tabella esistente se la tabella contiene valori duplicati nelle colonne chiave. Per risolvere questo problema, è necessario rimuovere le colonne chiave dalla definizione dell’indice o eliminare o aggiornare i valori duplicati.
Per impostazione predefinita, i valori duplicati non sono consentiti sulle colonne chiave, quando si dispone di un indice o vincolo unico. Per esempio, se si tenta di inserire 10 righe, di cui 5 righe contengono duplicati, allora tutte le 10 righe vengono respinte. Tuttavia, se voglio che solo le 5 righe duplicate siano rifiutate e accetto le 5 righe non duplicate, allora posso usare l’opzione IGNORE_DUP_KEY. Un esempio di utilizzo dell’opzione IGNORE_DUP_KEY è mostrato qui sotto.
CREATE UNIQUE INDEX IX_Employees_City ON Employees(City) WITH IGNORE_DUP_KEY
Limitazioni e restrizioni
L’indice unico, vincolo UNIQUE o PRIMARY KEY non può essere creato se esistono valori chiave duplicati nei dati.
Un indice unico non clusterizzato può contenere colonne non chiave incluse
Nel prossimo articolo, discuterò come usare l’indice nella clausola GROUP BY in SQL Server con esempi.