La creazione di un indice in SQL Server è un'attività moderatamente semplice che migliora le prestazioni della query. Possiamo creare un indice cluster e non cluster che può essere unico o non univoco come da requisito.
Se abbiamo già creato un indice su una tabella, tuttavia, a causa di una modifica del requisito, dobbiamo aggiungere / rimuovere una o più colonne a un indice esistente. In questo caso, possiamo usare il DROP_EXISTING opzione senza scrivere la riga di codice aggiuntiva per eliminare prima l'indice esistente e quindi ricrearlo. (Questo è uno stile di codifica vecchio stile: Elimina se esiste e crea un nuovo.)
Con DROP_EXISTING = ONSQL Server eliminerà e ricostruirà l'indice cluster / non cluster esistente con le specifiche dell'indice modificate, mantenendo il nome dell'indice come esistente. Possiamo aggiungere / rimuovere colonne, modificare l'ordinamento o modificare un filegroup.
DROP_EXISTING è impostato di default su OFFe non funziona se si include questa opzione durante la creazione di un nuovo indice per la prima volta. DROP_EXISTING = ON in SQL Server funziona solo se hai già un indice con il nome specificato su una tabella.
Con DROP_EXISTING, possiamo fare quanto segue:
1. È consentito modificare un indice non consolidato in un indice cluster.
Con DROP_EXISTING, non possiamo fare quanto segue:
1. La modifica di un indice cluster su qualsiasi altro tipo di indice non è consentita.
Vantaggi dell'uso di DROP_EXISTING = ON
Se stiamo ricreando un indice cluster su una tabella, che ha già un indice non in cluster, utilizzando DROP_EXISTING = ON in SQL Server mentre ricreare un indice cluster ha i seguenti vantaggi:
• Gli indici non in cluster non vengono ricostruiti di nuovo. Se stiamo ricreando un indice cluster con DROP_EXISTING = ON in SQL Server, è noto che la chiave di clustering non è stata modificata nell'indice Non cluster. Ciò consente di risparmiare una quantità significativa di tempo. Ogni indice non cluster ha una chiave di clustering che fa riferimento all'indice cluster. Se viene ricostruito un indice cluster, SQL Server deve modificare anche l'indice non cluster. Tuttavia, l'utilizzo di DROP_EXISTING = ON è vantaggioso poiché non è necessario ricreare nuovamente gli indici non cluster.
• Se utilizziamo il vecchio metodo per eliminare e ricreare un indice cluster, fa sì che tutto l'indice non in cluster su quella tabella venga ricostruito di nuovo in modo che corrisponda alla chiave di clustering. Questo può essere salvato usando DROP_EXISTING = ON con indice cluster.
Sintassi per la creazione dell'indice con DROP_EXISTING
CREARE [Unico] [Raggruppato | Non cluster] INDICE Index_Name ON (nome colonna / i) WITH DROP_EXISTING = {ON | OFF}; Esempi
Crea una tabella di esempio, Impiegato.
IF OBJECT_ID ('Employee', 'U') NON È Dipendente NULL DROP TABLE; CREATE TABLE Employee (Id INT CHIAVE PRIMARIA, Nome VARCHAR (50), Telefono VARCHAR (10), Genere CHAR (1), Paese VARCHAR (100), Stipendio DECIMALE (18,2)); 1. DROP_EXISTING = ON fallisce durante la creazione di un nuovo indice
Nell'esempio seguente, l'opzione DROP_EXISTING = ON tenta di trovare un indice esistente con NCI_Employee_TelphoneGender nome, poiché non esce, la creazione dell'indice fallisce con il seguente messaggio di errore.
CREA INDICE NONCLUSTERED NCI_Employee_TelphoneGender ON Dipendente (Telefono, Sesso) CON (DROP_EXISTING = ON);
Msg 7999, livello 16, stato 9, linea 1
Impossibile trovare un indice denominato "NCI_Employee_TelphoneGender" per la tabella "Dipendente".
Creare un indice
CREA INDICE NONCLUSTERED NCI_Employee_TelphoneGender ON Employee (Telephone, Gender);
Query per visualizzare l'elenco di colonne in un indice
La seguente query mostra che le colonne Telefono e Sesso sono state incluse nell'indice non clusterizzato creato.
seleziona nome come ColumnListInIndex da sys.index_columns come ic inner join sys.columns c su ic.column_id = c.column_id e ic.object_id = c.object_id dove ic.object_id = (seleziona object_id da sys.indexes dove name = 'NCI_Employee_TelphoneGender' e type_desc = 'NONCLUSTERED') e ic.index_id = 2;
2. DROP_EXISTING = ON in SQL Server funziona sull'indice esistente
Nell'esempio sopra, abbiamo creato un INDICE, NCI_Employee_TelphoneGender sul telefono e colonna di genere. Ora a causa di una modifica del requisito, dobbiamo includere una colonna Paese anche nell'indice non cluster esistente. Possiamo ottenere ciò usando l'opzione DROP_EXISTING = ON.
Nell'esempio seguente, con DROP_EXISTING = ON, SQL Server prima eliminerà l'indice specificato e creerà nuovamente un indice con lo stesso nome e colonna Paese inclusa.
CREA INDICE NONCLUSTERED NCI_Employee_TelphoneGender ON Dipendente (Telefono, Sesso, Paese) CON (DROP_EXISTING = ON);
Vecchio metodo per scrivere il codice qui sopra
Il codice sopra con DROP_EXISTING = ON è una sostituzione di un vecchio metodo di rilascio e ri-creazione di un indice. Se usiamo il codice qui sotto per eliminare / ricreare l'indice cluster, ricostruirà anche tutti gli indici non in cluster, il che potrebbe essere un grande successo in termini di prestazioni.
IF EXISTS (SELECT * FROM sys.indexes AS si WHERE si.name = 'NCI_Employee_TelphoneGender') DROP INDEX NCI_Employee_TelphoneGender ON Employee; GO CREATE INDEX NCI_Employee_TelphoneGender ON Dipendente (Telefono, Sesso, Paese) GO
Sommario
Abbiamo visto come DROP_EXISTING = ON in SQL Server ci aiuta con l'indice clustered a migliorare le prestazioni con pochi esempi e DROP_EXISTING = ON può essere utilizzato solo con l'indice creato esistente per modificare le loro specifiche.