→ È possibile creare un indice su una tabella. Indici in SQL Server. È possibile creare un indice non cluster solo su un sottoinsieme specifico di dati di una colonna chiave

È possibile creare un indice su una tabella. Indici in SQL Server. È possibile creare un indice non cluster solo su un sottoinsieme specifico di dati di una colonna chiave

In questo articolo per principianti, esaminerò come determinare gli indici necessari per aumentare la velocità di esecuzione delle query SQL.

In effetti, ci sono molte sottigliezze associate agli indici che possono influenzare in modo significativo le prestazioni sia in una direzione che nella direzione opposta. Puoi trovare molti articoli a riguardo su Internet. Articoli voluminosi che spiegano le differenze nell'indirizzamento, nell'archiviazione della memoria e molte altre cose.

Queste sono, ovviamente, cose davvero utili, ma spesso perdono una piccola sfumatura: i volumi di dati su cui tutte queste funzionalità hanno davvero un effetto notevole. E questa cifra viene solitamente misurata in centinaia di migliaia di record. In parole semplici, se nelle tue tabelle sono presenti circa 1-30 mila record e stiamo parlando di un sito Web (o di una risorsa simile) e non di una sorta di archivio dati intermedio per i sistemi caricati, molto spesso è più importante crea semplicemente gli indici corretti. È importante notare qui che non devi essere molto esperto tecnicamente. Molti indici utili possono essere creati utilizzando una logica semplice.

Nota: Ciò presuppone che le query stesse siano costruite in modo più o meno ottimale, ad esempio non ci sono campi aggiuntivi in ​​select , ecc.

Indice per campi identificatore intero.

Se hai un campo con un identificatore intero (non importa se è l'identificatore della tabella stessa o un identificatore che punta a una riga in un'altra tabella), crea un indice separato per esso.

Il punto è questo. Se il campo è un identificatore dei record della tabella stessa, allora parliamo di una chiave primaria (è anche un indice). I vantaggi derivanti da un tale indice sono numerosi, poiché i siti molto spesso operano con identificatori. Se si tratta di un identificatore di riga da una tabella di directory, è necessario anche un indice. Poiché se hai bisogno di dati filtrati, senza indici queste directory non sono molto utili (beh, forse solo la dimensione del database).

Se nel primo caso tutto è abbastanza semplice e chiaro, nel secondo caso (con un libro di consultazione) darò un semplice esempio.

Diciamo che ci sono due tabelle: articoli (articolo - id, nome, testo) e commenti (commento - id, articolo_id, testo). La prima tabella contiene 200 record (articoli), la seconda tabella contiene 2000 record (10 commenti per ogni articolo). Di conseguenza, quando ciascun utente apre un articolo, viene eseguita la seguente query:

Se la query SQL viene eseguita senza un indice per il campo article_id, l'intera tabella con i commenti (tutti i 2000 record) verrà scansionata completamente ogni volta. Se viene aggiunto un indice per il campo article_id, il database non dovrà esaminare più di 20 record (per essere precisi, circa 18 nel caso peggiore). Il calcolo qui è semplice. Nel peggiore dei casi, la ricerca dell'indice avviene approssimativamente alla velocità del logaritmo binario del numero di record + il numero di record con lo stesso valore del campo indice. In questo caso ogni articolo ha 10 record (i loro valori vengono ripetuti) + log2 di 200 (poiché ci sono solo 200 articoli = 2000/10) = 10 + 8 (arrotondato per eccesso) = 18.

Naturalmente, ciascuno di questi indici, oltre allo spazio su disco che occupa, introduce anche un ulteriore sovraccarico del database per inserimenti, aggiornamenti ed eliminazioni. Dopotutto, oltre a modificare i dati della tabella stessa, è anche necessario ricostruirne gli indici. Ma, come ho già detto, per il volume dei normali siti web questo non è un grosso problema. E anche se crei un indice su una tabella che non usi nelle tue query SQL, ciò non causerà alcun problema evidente. Inoltre, è sempre possibile che installando un modulo aggiuntivo o aggiungendo tu stesso le query, questo indice possa tornare molto utile.

Nota: Tuttavia, ricorda che questo si applica specificamente agli indici interi e non all'opzione "fammi creare indici per tutti i campi possibili".

Indici semplici e composti per le query più comuni.

Molti database dispongono di una cache dei risultati per le query. Prova a eseguire la stessa richiesta due volte di seguito: nel primo caso la richiesta richiederà molto tempo per essere completata, la seconda volta rapidamente. La prima volta che i dati verranno calcolati, la seconda volta i dati verranno forniti dalla cache. Tuttavia, ciò non è di grande aiuto nei casi in cui non è creata una cache per le query (ad esempio, quando il filtro contiene condizioni calcolate utilizzando le funzioni predefinite del database), nei casi in cui le query, sebbene dello stesso tipo, vengono utilizzate con risultati diversi parametri, e in quei casi, quando ci sono molte richieste e quindi i dati vengono archiviati nella cache per un periodo di tempo molto breve.

Pertanto, periodicamente può avere senso creare inoltre indici regolari e composti per le query eseguite di frequente. Diamo un'occhiata a due esempi.

Indice semplice.

Supponiamo che tu abbia una tabella - prodotti (prodotto - id, codice, nome, testo). E così accade che gli utenti del sito spesso cerchino i prodotti in base ai loro codici alfanumerici (articoli - campo codice). Di conseguenza, la richiesta assomiglia a questa:

In questa situazione ha senso creare un indice separato per il campo "codice", poiché con esso il database non dovrà scansionare completamente tutti i record della tabella. Tuttavia, tieni presente che i database potrebbero avere restrizioni sui tipi e sulle dimensioni dei campi. Pertanto dovresti prima verificare se è possibile creare un indice per tali campi.

Indice composito.

Prima di fornire un esempio con un indice composito, vorrei chiarire un po' un punto importante: l'ordine dei campi nell'indice è importante. Poiché la ricerca viene effettuata prima dal primo campo, poi da quello successivo (e così via). Pertanto, se si conosce il valore specifico solo dell'ultimo campo, un tale indice non sarà adatto, poiché senza conoscere il valore specifico del primo campo è impossibile determinare quale insieme di record deve essere controllato, ovvero perché il database dovrà scansionare tutti i record nella tabella. In parole semplici, l'indice (colonna_1, colonna_2) non è uguale all'indice (colonna_2, colonna_1).

Ora, supponiamo la seguente situazione. Sono presenti tre tabelle: utente (utente - id, nome), categoria (cat - id, nome) e articolo (articolo - id, cat_id, user_id, nome, testo). E hai fatto una cosa del genere sul sito: in fondo all'articolo viene visualizzato un elenco completo di articoli dello stesso utente di una determinata categoria. Allo stesso tempo, gli utenti si sono rivelati così prolifici da scrivere molti articoli, anche se in diverse categorie (ad esempio piccole storie, brevi note e così via). In questo caso la richiesta sarà simile a questa:

Se hai creato indici per i campi identificatore, questo ti aiuterà, ma non molto. Innanzitutto ci sono due indici ugualmente probabili. Uno per le categorie e il secondo per gli utenti. Quale sarà migliore è generalmente sconosciuto. Inoltre, questo potrebbe non essere di grande aiuto poiché gli utenti potrebbero avere 1000 articoli e le categorie potrebbero avere 1000 articoli. In secondo luogo, anche riducendo i record per un utente (o categoria) specifico, questi dovranno comunque essere scansionati utilizzando il secondo campo, ovvero una scansione completa (anche se per un volume inferiore di record). Ad esempio, se gli utenti hanno 1000 record, dovrai verificare per tutti i 1000 record se appartengono o meno alla categoria.

Per un numero elevato di record e chiamate frequenti, si tratta di una query SQL molto costosa. Pertanto in questo caso conviene creare un indice composito, ad esempio (user_id, cat_id): in questo caso, dopo la ricerca per utente, le ricerche successive per categoria saranno più veloci, poiché ci sarà anche un indice per il risultato record. Di conseguenza, invece di controllare 1000 record, ne verranno controllati molti meno (i controlli vengono calcolati come con un indice normale: logaritmo + numero di record).

Come è possibile determinare l'ordine dei campi in tali situazioni? Qui tutto è abbastanza semplice e simile a quello che ho descritto nell'articolo sul filtraggio (vedi il link all'inizio). Lascia che ti ricordi che il punto è che con ciascun filtro applicato, il numero di record diventa il più piccolo possibile. Pertanto, ha senso controllare il numero medio di record per ciascun valore di campo nella tabella. E il campo con questo numero in meno dovrebbe essere il primo. Ad esempio, per una determinata query SQL, vale la pena verificare quanto segue:

Calcola il numero medio di record per gli utenti select -- Numero medio di record avg(data.count) as avg from -- Raggruppa tutti i record per identificatore (select count(*) as `count` from article -- Raggruppa per utenti raggruppa per user_id) come dati; -- Calcola il numero medio di record per le categorie select -- Numero medio di record avg(data.count) as avg from -- Raggruppa tutti i record per id (select count(*) as `count` from article -- Raggruppa per categoria group by cat_id) come dati;

Di conseguenza, se il numero medio di utenti è inferiore, questo campo dovrebbe essere il primo, poiché dopo la prima ricerca ci saranno pochi record da controllare. Altrimenti, l'ID della categoria deve venire prima.

Tuttavia, vale la pena capire che in una situazione del genere vale anche la pena verificare che i record siano distribuiti più o meno uniformemente. Dopotutto, potrebbe risultare che 1 utente abbia scritto 2000 articoli e il resto solo 100. In una situazione del genere, potrebbe essere preferibile un filtro per categoria, poiché la maggior parte dei lettori visualizzerà gli articoli di questo particolare utente. Pertanto, a volte vale la pena calcolare solo il raggruppamento per identificatori (senza calcolare la media) e visualizzare rapidamente i risultati.

Se devi creare un indice per tre o più campi, dovresti fare lo stesso, aumentando solo il numero di campi per i quali viene effettuato il raggruppamento per identificatore. In parole semplici, controlla prima il primo campo e determina il numero più piccolo, quindi invece di "raggruppa per colonna_1" indica varie opzioni con i campi rimanenti sotto forma di "raggruppa per colonna_1, colonna_2", quindi "raggruppa per colonna_1, colonna_3" e così via. In questo caso, ognuno sceglie quelle combinazioni in cui il numero medio di record diventa sempre più piccolo.

Materiale teorico

Gli indici ti consentono di trovare informazioni in enormi database nel modo più efficiente possibile.

SQL Server 2008 supporta due tipi fondamentali di indici: cluster e non cluster. Entrambi i tipi di indici sono implementati come un albero bilanciato (albero B), in cui il livello delle foglie si trova al livello inferiore della struttura. La differenza tra i due tipi di indici è che un indice cluster fornisce l'ordinamento fisico dei dati su disco. Un indice cluster è sparso: i puntatori nelle foglie dell'albero B puntano alla pagina dei dati.

Un indice non cluster è denso e contiene solo le colonne incluse nella chiave dell'indice. Negli indici densi, i puntatori nelle foglie dell'albero B puntano a righe di dati effettivi. Se per una tabella non è definito un indice cluster, viene denominata tabella heap o non ordinata. In quest'ultimo caso, la tabella è organizzata fisicamente (ordinata) nell'ordine in cui vengono aggiunti i nuovi record, a differenza delle tabelle con indici cluster, che sono ordinate in base ai valori della chiave di ordinamento. Si può dire che una tabella può essere rappresentata in due forme, come heap o come indice cluster.

Indici cluster

Gli indici cluster possono essere creati in base a una o più colonne di tabella: tale indice è chiamato chiave di indice e presenta una serie di restrizioni:

Le colonne in un indice cluster sono chiamate chiave di clustering. Un indice cluster ha un impatto speciale su SQL Server perché impone di ordinare i dati in una tabella in base alla chiave di clustering. Poiché una tabella può essere ordinata solo in un modo, può avere un solo indice cluster.

Gli indici cluster specificano l'ordinamento dei dati in una tabella. Tuttavia, gli indici cluster non forniscono l'ordinamento fisico. Un indice cluster non organizza fisicamente i dati sul disco perché ciò comporterebbe molte operazioni di I/O su disco quando le pagine vengono divise. Garantisce solo che la catena di pagine indicizzate sia ordinata logicamente, consentendo a SQL Server di spostarsi direttamente attraverso la catena di pagine durante la ricerca di dati. Mentre SQL Server si sposta attraverso una catena di pagine indicizzate, le righe di dati vengono lette in base alla chiave di clustering.

Indice non cluster

Un indice non cluster non impone alcuna restrizione sull'ordine dei record in una tabella, quindi puoi creare molti indici non cluster sulla stessa tabella, ma questi indici hanno le stesse restrizioni degli indici cluster:

Un indice può estendersi su un massimo di 16 colonne;

La dimensione massima della chiave dell'indice è 900 byte.

Il livello foglia di un indice non cluster contiene un puntatore ai dati desiderati. Se la tabella dispone di un indice cluster, il livello foglia dell'indice non cluster punta alla chiave di clustering. Se non è presente alcun indice cluster, le pagine a livello foglia puntano a righe di dati nella tabella.

La sintassi generale per creare un indice relazionale è:

CREA INDICE nome_indice

SU<объект>(colonna [, … n])

[ ; ]

Indice composito

È possibile creare un indice composito in base a più campi. In questo caso valgono le limitazioni descritte in precedenza. Se l'indice è costruito su campi con dimensione fissa, la somma delle lunghezze di questi campi non deve superare questi 900 byte; se l'indice è costruito su campi con lunghezza variabile, la somma delle dimensioni massime dei campi può superare i 900 byte , ma il valore delle somme per ciascun record non può superare i 900 byte. Ad esempio, una tabella ha due campi a lunghezza variabile di 500 byte ciascuno. SQL Server consente di creare una chiave composita basata su questi due campi se non sono presenti record la cui lunghezza per entrambi i campi superi i 900 byte. Vale la pena prestare attenzione al fatto che l'indice composito per (Colonna1, Colonna2) è diverso da (Colonna2, Colonna1), così come dagli indici creati separatamente su questi due campi.

Frammentazione dell'indice

I file del sistema operativo in genere si frammentano nel tempo a causa di scritture ripetute. Anche gli indici possono frammentarsi, ma la frammentazione degli indici è diversa dalla frammentazione dei file.

Quando crei un indice, tutti i valori delle chiavi dell'indice vengono scritti in modo ordinato nelle pagine dell'indice. Quando si elimina una riga da una tabella, SQL Server deve eliminare la voce corrispondente nell'indice, creando dei buchi nella pagina dell'indice. SQL Server non recupera lo spazio liberato perché il costo del rilevamento e del riutilizzo dei buchi nell'indice è troppo elevato. Se il valore nella tabella di base cambia, SQL Server sposta il record del puntatore in un'altra posizione, creando così un altro foro. Quando le pagine dell'indice diventano piene ed è necessaria la suddivisione della pagina, l'indice diventa nuovamente frammentato. Nel tempo, gli indici delle tabelle in cui si verificano le modifiche ai dati diventano frammentati.

Per controllare il grado di frammentazione dell'indice, viene comunemente utilizzato un parametro chiamato fattore di riempimento. Per eliminare la frammentazione, puoi anche utilizzare l'istruzione ALTER INDEX. Il fattore di riempimento è un parametro dell'indice che specifica la percentuale di spazio libero riservato su ciascuna pagina a livello foglia quando viene creato o ricostruito un indice. Lo spazio riservato consente di allocare valori aggiuntivi in ​​futuro, riducendo così il numero di suddivisioni della pagina. Il fattore di riempimento viene misurato in percentuali intere; ad esempio, un valore di 75 significa che ogni pagina foglia creata deve contenere il 25% di spazio libero per accogliere i valori futuri.

Deframmentazione degli indici

Poiché SQL Server non restituisce spazio al sistema, è necessario recuperare periodicamente lo spazio vuoto nell'indice per mantenere i vantaggi in termini di prestazioni che hanno inizialmente creato l'indice. Per deframmentare gli indici, utilizzare l'istruzione ALTER INDEX.

ALTER INDEX ( nome_indice | TUTTI )

SU

[ CON ( [ ,...N ]) ]

| [ PARTIZIONE =numero_partizione

[ CON (

[ PARTIZIONE =numero_partizione ]

[ CON (LOB_COMPACTION = (ON | OFF))]

| IMPOSTATO ( [ ,...N ])

Durante la deframmentazione degli indici, è possibile selezionare le opzioni RICOSTRUISCI o RIORGANIZZA.

Il primo parametro ricostruisce tutti i livelli di indice e riempie le pagine in base al parametro del fattore di riempimento. Quando si ricostruisce un indice cluster, viene ricostruito solo quell'indice, ma se si specifica l'opzione ALL, vengono ricostruiti sia l'indice cluster che tutti gli indici non cluster sulla tabella. Una ricostruzione dell'indice aggiorna l'intera struttura ad albero B, pertanto, a meno che non venga specificato ONLINE, la tabella viene bloccata fino al completamento della ricostruzione. Ad esempio, per ricostruire l'indice IX_BillID sulla tabella BillItem, è necessario eseguire la seguente query:

ALTERARE INDICE IX_BillID

L'opzione RIORGANIZZA elimina la deframmentazione solo a livello foglia. Le pagine intermedie e la pagina principale non vengono deframmentate. L'operazione REORGANIZE viene sempre eseguita in linea, pertanto non provoca un blocco della tabella a lungo termine. Ad esempio, per riorganizzare l'indice IX_BillID nella tabella BillItem, eseguire la query seguente:

ALTERARE INDICE IX_BillID

Lavorare con gli indici in MS SQL Server Management Studio

Per vedere quali indici sono stati creati è necessario aprire la scheda Indice della tabella Fatture nel pannello Esplora oggetti. Il percorso completo della scheda: Databases ® EducationDatabase ® Tables ® [nome tabella] ® Indexes è mostrato nella Figura 1.1. Secondo la figura, per questa tabella è stato creato un indice cluster PK_Bill.

Controlla tu stesso la presenza di indici cluster su tutte le tabelle del database.

Figura 1.1 – Esplora oggetti, scheda Indici espansa

Creiamo un indice aggiuntivo sul campo chiave esterna BillID della tabella BillItem. Esistono due modi per creare un indice:

Esecuzione della query CREATE INDEX. Creiamo una query in una nuova scheda facendo clic sul pulsante Nuova query sulla barra degli strumenti standard. La barra degli strumenti è mostrata nella Figura 1.2.

Figura 1.2 – Barra degli strumenti

Dopo aver aperto una nuova scheda, eseguiamo la richiesta mostrata nella Figura 1.3. Per eseguire la richiesta è necessario fare clic sul pulsante Esegui sulla barra degli strumenti (Figura 1.2) oppure premere il tasto F5 sulla tastiera.

Utilizzando la GUI di Microsoft SQL Server Management Studio. Nel menu contestuale, scheda Indici, selezionare Nuovo indice, come mostrato nella Figura 1.4.

Figura 1.4 – Menu contestuale della scheda Indici

Nella finestra visualizzata è necessario specificare il nome dell'indice, gli attributi di ordinamento e il tipo di indice (indice XML cluster, non cluster o primario). Se una tabella dispone già di un indice cluster, quando provi a creare un nuovo indice cluster, il sistema ti avviserà della possibilità di eliminare l'indice esistente e crearne uno nuovo. Quando crei un indice cluster, tutti gli indici non cluster vengono ricostruiti.

Inoltre, nella finestra di creazione dell'indice, puoi specificare un flag per supportare l'unicità dei valori nei campi indicizzati. Avere un indice di questo tipo impedirà l'aggiunta di valori duplicati ai campi indicizzati.

1. Verificare la presenza di indici sui campi chiave della tabella. Se necessario, creare indici cluster. Per creare un nuovo indice, utilizzare il comando CREATE INDEX oppure in Microsoft SQL Management Studio, nella sezione Tabelle/nome_tabella/Indici, utilizzare il comando Nuovo indice....

2. Creare indici non cluster sui campi chiave esterna delle tabelle del database. Spiegare perché sono necessari tali indici?

3. Creare indici non cluster nei campi informazioni: Nome e Data in tutte le tabelle del database. Spiegare perché sono necessari tali indici?

4. Per un indice cluster e un indice basato sul campo Data della tabella dei record in una ricevuta, ottenere informazioni sulle proprietà estese degli indici. Spiegare il significato delle informazioni fornite nella sezione Frammentazione della pagina Proprietà. Spiegare come vengono calcolati la profondità dell'albero dell'indice, il numero di foglie e il fattore di frammentazione.

5. Ricostruire l'indice cluster sulla tabella BillItem utilizzando il comando ALTER INDEX o utilizzando il comando Ricostruisci dal menu contestuale dell'indice.

6. Preparare il materiale da includere nella presentazione del rapporto per i database: corso speciale.

In questo articolo vengono illustrati gli indici e il relativo ruolo nell'ottimizzazione del tempo di esecuzione delle query. La prima parte dell'articolo discute le diverse forme di indici e come memorizzarli. Successivamente, esamineremo le tre istruzioni Transact-SQL principali utilizzate per utilizzare gli indici: CREATE INDEX, ALTER INDEX e DROP INDEX. Viene quindi considerata la frammentazione degli indici del suo impatto sulle prestazioni del sistema. Fornisce quindi alcune linee guida generali per la creazione di indici e descrive diversi tipi speciali di indici.

informazioni generali

I sistemi di database utilizzano in genere gli indici per fornire un accesso rapido ai dati relazionali. Un indice è una struttura dati fisica separata che consente un rapido accesso a una o più righe di dati. Pertanto, l'ottimizzazione corretta degli indici è un aspetto chiave per migliorare le prestazioni delle query.

L'indice di un database è per molti versi simile all'indice (indice alfabetico) di un libro. Quando abbiamo bisogno di trovare rapidamente un argomento in un libro, guardiamo prima nell'indice in quali pagine del libro viene discusso questo argomento, quindi apriamo immediatamente la pagina desiderata. Allo stesso modo, quando si cerca una riga specifica in una tabella, il Motore di database accede all'indice per trovarne la posizione fisica.

Ma ci sono due differenze significative tra l'indice di un libro e l'indice di un database:

    Il lettore del libro ha la possibilità di decidere autonomamente se utilizzare o meno l'indice in ciascun caso specifico. L'utente del database non ha questa opportunità e questa decisione viene presa per lui da un componente di sistema chiamato ottimizzatore di query. (L'utente può manipolare l'uso degli indici tramite i suggerimenti sugli indici, ma si consiglia di utilizzare questi suggerimenti solo in un numero limitato di casi speciali.)

    L'indice per una cartella di lavoro specifica viene creato insieme alla cartella di lavoro, dopodiché non viene più modificato. Ciò significa che l'indice di un particolare argomento punterà sempre allo stesso numero di pagina. Al contrario, l'indice del database può cambiare ogni volta che cambiano i dati corrispondenti.

Se una tabella non dispone di un indice adatto, il sistema utilizza un metodo di scansione della tabella per recuperare le righe. Espressione scansione della tabella significa che il sistema recupera ed esamina in sequenza ogni riga della tabella (dalla prima all'ultima) e inserisce la riga nel set di risultati se la condizione di ricerca nella clausola WHERE è soddisfatta. Pertanto, tutte le righe vengono recuperate in base alla loro posizione fisica in memoria. Questo metodo è meno efficiente dell'accesso tramite indici, come spiegato di seguito.

Gli indici sono memorizzati in strutture di database aggiuntive chiamate pagine indice. Per ogni riga indicizzata c'è voce dell'indice, che viene salvato nella pagina dell'indice. Ogni elemento dell'indice è costituito da una chiave dell'indice e da un indice. Questo è il motivo per cui l'elemento indice è significativamente più corto della riga della tabella a cui punta. Per questo motivo, il numero di elementi dell'indice in ciascuna pagina di indice è molto maggiore del numero di righe nella pagina di dati.

Questa proprietà degli indici è molto importante perché il numero di operazioni di I/O richieste per attraversare le pagine di indice è significativamente inferiore al numero di operazioni di I/O richieste per attraversare le pagine di dati corrispondenti. In altre parole, la scansione di una tabella richiederebbe probabilmente molte più operazioni di I/O rispetto alla scansione dell'indice della tabella.

Gli indici del Motore di database vengono creati utilizzando una struttura dati ad albero B+. Un albero B+ ha una struttura ad albero in cui tutti i nodi più in basso si trovano allo stesso numero di livelli di distanza dalla parte superiore (nodo radice) dell'albero. Questa proprietà viene mantenuta anche quando i dati vengono aggiunti o rimossi dalla colonna indicizzata.

La figura seguente mostra la struttura ad albero B+ per la tabella Employee e l'accesso diretto alla riga in tale tabella con il valore 25348 per la colonna Id. (Supponiamo che la tabella Employee sia indicizzata dalla colonna Id.) Puoi anche vedere in questa figura che un albero B+ è costituito da un nodo radice, nodi dell'albero e zero o più nodi intermedi:

È possibile cercare in questo albero il valore 25348 come segue. Partendo dalla radice dell'albero, cerca il valore chiave più piccolo maggiore o uguale al valore richiesto. Pertanto, nel nodo radice questo valore sarà 29346, quindi verrà effettuata una transizione al nodo intermedio associato a questo valore. In questo nodo, il valore 28559 soddisfa i requisiti specificati, a seguito dei quali viene effettuata una transizione al nodo dell'albero associato a questo valore. Questo nodo contiene il valore desiderato 25348. Dopo aver determinato l'indice richiesto, possiamo estrarre la sua riga dalla tabella dati utilizzando gli appositi puntatori. (Un approccio equivalente alternativo sarebbe quello di cercare un valore inferiore o uguale all'indice.)

La ricerca indicizzata è solitamente il metodo preferito per cercare tabelle con un numero elevato di righe a causa dei suoi ovvi vantaggi. Utilizzando la ricerca indicizzata, possiamo trovare qualsiasi riga in una tabella in brevissimo tempo utilizzando solo poche operazioni di I/O. Inoltre, la ricerca sequenziale (ovvero la scansione di una tabella dalla prima riga all'ultima) richiede più tempo, quanto più è lontana la riga richiesta.

Nelle sezioni seguenti esamineremo i due tipi esistenti di indici, cluster e non cluster, e impareremo come creare indici.

Indici cluster

Indice cluster determina l'ordine fisico dei dati in una tabella. Il Motore di database consente di creare un solo indice cluster per una tabella, perché Le righe di una tabella non possono essere ordinate fisicamente in più di un modo. Una ricerca utilizzando un indice cluster viene eseguita dal nodo radice di un albero B+ verso i nodi dell'albero che sono collegati insieme in un elenco doppiamente collegato chiamato catena di pagine.

Una proprietà importante di un indice cluster è che i suoi nodi dell'albero contengono pagine di dati. (Tutti gli altri livelli di nodi di indice cluster contengono pagine di indice.) Una tabella in cui è definito un indice cluster (esplicitamente o implicitamente) è denominata tabella cluster. La struttura ad albero B+ di un indice cluster è mostrata nella figura seguente:

Per impostazione predefinita, viene creato un indice cluster su ogni tabella che dispone di una chiave primaria definita da un vincolo di chiave primaria. Inoltre, ogni indice cluster è unico per impostazione predefinita, ovvero In una colonna in cui è definito un indice cluster, ogni valore di dati può essere visualizzato solo una volta. Se viene creato un indice cluster su una colonna che contiene valori duplicati, il sistema di database garantisce l'univocità aggiungendo un identificatore di quattro byte alle righe che contengono valori duplicati.

Gli indici cluster forniscono un accesso ai dati molto rapido quando una query cerca un intervallo di valori.

Indici non cluster

La struttura di un indice non cluster è esattamente la stessa di un indice cluster, ma con due importanti differenze:

    un indice non cluster non modifica l'ordinamento fisico delle righe della tabella;

    Le pagine dei nodi indice non cluster sono costituite da chiavi di indice e segnalibri.

Se definisci uno o più indici non cluster su una tabella, l'ordine fisico delle righe della tabella non verrà modificato. Per ogni indice non cluster, Motore di database crea una struttura di indice aggiuntiva archiviata nelle pagine di indice. La struttura ad albero B+ di un indice non cluster è mostrata nella figura seguente:

Un segnalibro in un indice non cluster indica dove si trova la riga corrispondente alla chiave dell'indice. Il componente segnalibro della chiave dell'indice può essere di due tipi, a seconda che la tabella sia una tabella in cluster o un heap. (Nella terminologia di SQL Server, un heap è una tabella senza un indice cluster.) Se esiste un indice cluster, la scheda dell'indice non cluster mostra l'albero B+ dell'indice cluster della tabella. Se la tabella non dispone di un indice cluster, il segnalibro è identico identificatore di riga (RID - Identificatore di riga), composto da tre parti: l'indirizzo del file in cui è archiviata la tabella, l'indirizzo del blocco fisico (pagina) in cui è archiviata la riga e l'offset della riga nella pagina.

Come accennato in precedenza, la ricerca dei dati utilizzando un indice non cluster può essere eseguita in due modi diversi, a seconda del tipo di tabella:

    heap: attraversa la struttura di ricerca di un indice non cluster, dopodiché la riga viene recuperata utilizzando l'identificatore di riga;

    tabella clusterizzata: un attraversamento di ricerca di una struttura di indici non cluster seguita da un attraversamento dell'indice cluster corrispondente.

In entrambi i casi, la quantità di operazioni di I/O è piuttosto elevata, pertanto è necessario progettare un indice non cluster con cautela e utilizzarlo solo se si è certi che il suo utilizzo migliorerà significativamente le prestazioni.

Linguaggio e indici Transact-SQL

Ora che abbiamo familiarità con la struttura fisica degli indici, in questa sezione vedremo come creare, modificare ed eliminare gli indici, nonché come ottenere informazioni sulla frammentazione dell'indice e modificare le informazioni sull'indice. Tutto ciò ci preparerà per la successiva discussione sull'utilizzo degli indici per migliorare le prestazioni del sistema.

Creazione di indici

Un indice su una tabella viene creato utilizzando l'istruzione CREA INDICE. Questa istruzione ha la seguente sintassi:

CREATE INDEX nome_indice ON nome_tabella (colonna1 ,...) [ INCLUDE (nome_colonna [ ,... ]) ] [[, ] PAD_INDEX = (ON | OFF)] [[, ] DROP_EXISTING = (ON | OFF)] [[ , ] ORDINA_IN_TEMPDB = (ON | OFF)] [[, ] IGNORE_DUP_KEY = (ON | OFF)] [[, ] ALLOW_ROW_LOCKS = (ON | OFF)] [[, ] ALLOW_PAGE_LOCKS = (ON | OFF)] [[, ] STATISTICS_NORECOMPUTE = (ON | OFF)] [[, ] ONLINE = (ON | OFF)]] Convenzioni di sintassi

Il parametro index_name specifica il nome dell'indice da creare. È possibile creare un indice su una o più colonne di una singola tabella, identificata dal parametro table_name. La colonna su cui viene creato l'indice è specificata dal parametro colonna1. Il suffisso numerico di questo parametro indica che l'indice può essere creato su più colonne della tabella. Il Motore di database supporta anche la creazione di indici sulle visualizzazioni.

Puoi indicizzare qualsiasi colonna della tabella. Ciò significa che è possibile indicizzare anche le colonne contenenti valori del tipo di dati VARBINARY(max), BIGINT e SQL_VARIANT.

L'indice può essere semplice o composito. Un indice semplice viene creato su una singola colonna, mentre un indice composto viene creato su più colonne. Un indice composto presenta alcune limitazioni relative alle dimensioni e al numero di colonne. Un indice può avere un massimo di 900 byte e un massimo di 16 colonne.

Parametro UNICO specifica che la colonna indicizzata può contenere solo valori a valore singolo (ovvero non ripetuti). In un indice composito a valore singolo, l'unico deve essere la combinazione dei valori di tutte le colonne di ciascuna riga. Se la parola chiave UNIQUE non è specificata, sono consentiti valori duplicati nelle colonne indicizzate.

Parametro CLUSTERED specifica un indice cluster e Parametro NON CLUSTERED(impostazione predefinita) specifica che l'indice non modifica l'ordine delle righe nella tabella. Il Motore di database consente un massimo di 249 indici non cluster in una tabella.

Il Motore di database è stato migliorato per supportare gli indici con ordine decrescente dei valori delle colonne. Il parametro ASC dopo il nome della colonna specifica che l'indice viene creato con l'ordine crescente dei valori della colonna e il parametro DESC specifica l'ordine decrescente dei valori della colonna dell'indice. Ciò fornisce una maggiore flessibilità nell'uso dell'indice. Con l'ordine discendente, dovresti creare indici compositi su colonne i cui valori sono ordinati in direzioni opposte.

Parametro INCLUDI Consente di specificare colonne non chiave che vengono aggiunte alle pagine del nodo di un indice non cluster. I nomi delle colonne nell'elenco INCLUDE non devono essere ripetuti e una colonna non può essere utilizzata sia come colonna chiave che come colonna non chiave.

Per comprendere veramente l'utilità del parametro INCLUDE, è necessario capire di cosa si tratta indice di copertura. Se tutte le colonne della query sono incluse nell'indice, puoi ottenere miglioramenti significativi delle prestazioni perché L'ottimizzatore di query può individuare tutti i valori delle colonne nelle pagine dell'indice senza accedere ai dati nella tabella. Questa funzionalità è denominata indice di copertura o query di copertura. Pertanto, includere colonne aggiuntive non chiave nelle pagine dei nodi indice non cluster consentirà di ottenere più query di copertura e di migliorarne significativamente le prestazioni.

Parametro FILLFACTOR specifica la percentuale di ciascuna pagina dell'indice da riempire al momento della creazione dell'indice. Il valore del parametro FILLFACTOR è impostabile nell'intervallo da 1 a 100. Con il valore n=100 ogni pagina indice viene riempita al 100%, cioè una pagina nodo esistente così come una pagina non nodo non avrà spazio libero per inserire nuove righe. Pertanto si consiglia di utilizzare questo valore solo per le tabelle statiche. (Il valore predefinito, n=0, significa che le pagine del nodo indice sono piene e ciascuna delle pagine intermedie contiene spazio libero per una voce.)

Se il parametro FILLFACTOR è impostato su valori compresi tra 1 e 99, le pagine dei nodi della struttura dell'indice che viene creata conterranno spazio libero. Maggiore è il valore di n, minore è lo spazio libero presente nelle pagine del nodo indice. Ad esempio, con n=60, ciascuna pagina del nodo indice avrà il 40% di spazio libero per futuri inserimenti di righe indice. (Le righe dell'indice vengono inserite utilizzando un'istruzione INSERT o UPDATE.) Pertanto, un valore di n=60 sarebbe ragionevole per le tabelle i cui dati cambiano abbastanza frequentemente. Per valori FILLFACTOR compresi tra 1 e 99, le pagine indice intermedie contengono spazio libero per una voce ciascuna.

Una volta creato un indice, il valore FILLFACTOR non è supportato durante l'uso. In altre parole, indica solo la quantità di spazio riservato con i dati disponibili quando si imposta la percentuale di spazio libero. Per ripristinare il parametro FILLFACTOR al suo valore originale, utilizzare l'istruzione ALTER INDEX.

Parametro PAD_INDEXè strettamente correlato al parametro FILLFACTOR. Il parametro FILLFACTOR specifica sostanzialmente la quantità di spazio libero come percentuale della dimensione totale della pagina dei nodi dell'indice. Inoltre, il parametro PAD_INDEX specifica che il valore del parametro FILLFACTOR si applica sia alle pagine di indice che alle pagine di dati nell'indice.

Parametro DROP_EXISTING Consente di migliorare le prestazioni durante la riproduzione di un indice cluster su una tabella che contiene anche un indice non cluster. Per ulteriori informazioni, vedere la sezione "Ricostruzione dell'indice" di seguito.

Parametro SORT_IN_TEMPDB utilizzato per inserire i dati dalle operazioni di ordinamento intermedie utilizzate durante la creazione di un indice nel database di sistema tempdb. Ciò potrebbe migliorare le prestazioni se tempdb si trova su un disco diverso da quello dei dati.

Parametro IGNORE_DUP_KEY Consente al sistema di ignorare un tentativo di inserire valori duplicati nelle colonne indicizzate. Questa opzione deve essere utilizzata solo per evitare di interrompere una transazione con esecuzione prolungata quando un'istruzione INSERT inserisce dati duplicati in una colonna indicizzata. Quando questa opzione è abilitata, quando un'istruzione INSERT tenta di inserire righe in una tabella che violano l'unicità dell'indice, il sistema del database emette semplicemente un avviso invece di mandare in crash l'intera istruzione. In questo caso, il Motore di database non inserisce righe con valori di chiave duplicati, ma semplicemente le ignora e aggiunge le righe corrette. Se questo parametro non è impostato, l'esecuzione dell'intera istruzione terminerà in modo anomalo.

Quando parametro ALLOW_ROW_LOCKS attivato (impostato su on), il sistema applica il blocco delle file. Allo stesso modo, quando attivato parametro ALLOW_PAGE_LOCKS, il sistema applica il blocco della pagina durante l'accesso simultaneo. Parametro STATISTICS_NORECOMPUTE determina lo stato del ricalcolo automatico delle statistiche per l'indice specificato.

Attivato Parametro ONLINE consente di creare, ricreare ed eliminare un indice in modalità di dialogo. Questa opzione consente di modificare contemporaneamente i dati della tabella principale o dell'indice cluster e di eventuali indici associati durante la modifica dell'indice. Ad esempio, durante la ricreazione di un indice cluster, è possibile continuare ad aggiornarne i dati ed eseguire query su tali dati.

Parametro attivo crea l'indice specificato nel filegroup predefinito (valore predefinito) o nel filegroup specificato (valore file_group).

L'esempio seguente mostra come creare un indice non cluster nella colonna Id della tabella Employee:

USA CampioneDb; CREATE INDEX ix_empid ON Dipendente(Id);

La creazione di un indice composito a valore singolo è mostrata nell'esempio seguente:

USA CampioneDb; CREA INDICE UNICO ix_empid_prnu ON Works_on (EmpId, ProjectNumber) CON FILLFACTOR= 80;

In questo esempio, i valori in ciascuna colonna devono essere a cifra singola. Quando viene creato un indice, viene riempito l'80% dello spazio su ciascuna pagina del nodo indice.

Non è possibile creare un indice univoco su una colonna se la colonna contiene valori duplicati. Un indice di questo tipo può essere creato solo se ciascun valore (compresi i valori NULL) appare esattamente una volta nella colonna. Inoltre, qualsiasi tentativo di inserire o modificare un valore di dati esistente in una colonna inclusa in un indice univoco esistente verrà rifiutato dal sistema se il valore viene duplicato.

Ottenere informazioni sulla frammentazione dell'indice

Durante la vita di un indice, questo può frammentarsi, rendendo inefficiente il processo di archiviazione dei dati nelle pagine dell'indice. Esistono due tipi di frammentazione dell'indice: frammentazione interna e frammentazione esterna. La frammentazione interna determina la quantità di dati archiviati in ciascuna pagina, mentre la frammentazione esterna si verifica quando le pagine non sono in ordine logico.

Per ottenere informazioni sulla frammentazione dell'indice interno, è stata chiamata una vista di gestione dinamica DMV sys.dm_db_index_physical_stats. Questo DMV restituisce informazioni sul volume e sulla frammentazione dei dati e degli indici della pagina specificata. Per ogni pagina viene restituita una riga per ogni livello dell'albero B+. Utilizzando questo DMV è possibile ottenere informazioni sul grado di frammentazione delle righe nelle pagine di dati, in base alle quali è possibile decidere se riorganizzare i dati.

L'uso della vista sys.dm_db_index_physical_stats è illustrato nell'esempio seguente. (Prima di eseguire l'esempio batch, è necessario eliminare tutti gli indici esistenti nella tabella Works_on. Per eliminare gli indici, utilizzare l'istruzione DROP INDEX, mostrata più avanti.)

USA CampioneDb; DICHIARARE @dbId INT; DICHIARARE @tabId INT; DICHIARARE @indId INT; SET @dbId = DB_ID("CampioneDb"); SET @tabId = OBJECT_ID("Dipendente"); SELEZIONA avg_fragmentation_in_percent, avg_page_space_used_in_percent DA sys.dm_db_index_physical_stats (@dbId, @tabId, NULL, NULL, NULL);

Come puoi vedere dall'esempio, la vista sys.dm_db_index_physical_stats ha cinque parametri. I primi tre parametri definiscono rispettivamente gli ID del database, della tabella e dell'indice correnti. Il quarto parametro specifica l'ID della partizione e l'ultimo parametro specifica il livello di scansione utilizzato per ottenere informazioni statistiche. (Il valore predefinito per un particolare parametro può essere specificato utilizzando il valore NULL.)

Le colonne più importanti in questa visualizzazione sono le colonne avg_fragmentation_in_percent e avg_page_space_used_in_percent. Il primo indica il livello medio di frammentazione in percentuale, mentre il secondo determina la quantità di spazio occupato in percentuale.

Modifica delle informazioni sull'indice

Una volta acquisita familiarità con le informazioni sulla frammentazione dell'indice descritte nella sezione precedente, è possibile modificare queste e altre informazioni sull'indice utilizzando i seguenti strumenti di sistema:

    viste della directory sys.indexes;

    viste del catalogo sys.index_columns;

    procedura di sistema sp_helpindex;

    funzioni di proprietà della proprietà dell'oggetto;

    Ambiente di gestione di SQL Server Management Studio;

    Vista a gestione dinamica DMV sys.dm_db_index_usage_stats;

    Vista a gestione dinamica DMV sys.dm_db_missing_index_details.

Visualizzazione del catalogo sys.indexes contiene una riga per ogni indice e una riga per ogni tabella senza indice cluster. Le colonne più importanti di questa vista del catalogo sono le colonne object_id, name e index_id. La colonna object_id contiene il nome dell'oggetto database che possiede l'indice e le colonne name e index_id contengono rispettivamente il nome e l'ID di tale indice.

Visualizzazione del catalogo sys.index_columns contiene una riga per ogni colonna che fa parte dell'indice o dell'heap. Queste informazioni possono essere utilizzate insieme alle informazioni ottenute tramite la vista del catalogo sys.indexes per ottenere informazioni aggiuntive sulle proprietà dell'indice specificato.

Procedura di sistema sp_helpindex restituisce informazioni sugli indici delle tabelle e informazioni statistiche per le colonne. Questa procedura ha la seguente sintassi:

sp_helpindex [@db_object =] "nome"

Qui la variabile @db_object rappresenta il nome della tabella.

In relazione agli indici, funzione di proprietà dell'oggetto ha due proprietà: IsIndexed e IsIndexable. La prima proprietà fornisce informazioni sull'eventuale presenza di un indice nella tabella o nella vista, mentre la seconda proprietà indica se la tabella o la vista è indicizzabile.

Per modificare le informazioni sull'indice esistente utilizzando SQL Server Management Studio, selezionare il database desiderato nella cartella Database, espandere il nodo Tabelle e in quel nodo espandere la tabella desiderata e la relativa cartella Indici. La cartella Indici della tabella visualizzerà un elenco di tutti gli indici esistenti per quella tabella. Facendo doppio clic su un indice si aprirà la finestra di dialogo Proprietà indice con le proprietà di quell'indice. (È inoltre possibile creare un nuovo indice o eliminarne uno esistente utilizzando Management Studio.)

Prestazione sys.dm_db_index_usage_stats restituisce un conteggio dei diversi tipi di operazioni sull'indice e l'ultima volta che è stato eseguito ciascun tipo di operazione. Ogni operazione di ricerca, ricerca o aggiornamento separata su un indice specificato in una singola query è considerata un utilizzo dell'indice e incrementa di uno il contatore corrispondente in quella DMV. In questo modo, puoi ottenere informazioni generali sulla frequenza con cui viene utilizzato un indice, in modo da poterlo utilizzare per determinare quali indici vengono utilizzati di più e quali meno.

Prestazione sys.dm_db_missing_index_details Restituisce informazioni dettagliate sulle colonne della tabella per le quali non sono presenti indici. Le colonne più importanti di questo DMV sono le colonne index_handle e object_id. Il valore nella prima colonna identifica l'indice mancante specifico e il valore nella seconda colonna identifica la tabella in cui manca l'indice.

Modifica degli indici

Il Motore di database è uno dei pochi sistemi di database che supporta l'istruzione ALTERARE L'INDICE. Questa istruzione può essere utilizzata per eseguire operazioni di manutenzione dell'indice. La sintassi dell'istruzione ALTER INDEX è molto simile alla sintassi dell'istruzione CREATE INDEX. In altre parole, questa istruzione consente di modificare i valori dei parametri ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY e STATISTICS_NORECOMPUTE descritti in precedenza nell'istruzione CREATE INDEX.

Oltre alle opzioni di cui sopra, l'istruzione ALTER INDEX supporta altre tre opzioni:

    Parametro RICOSTRUZIONE, utilizzato per ricreare l'indice;

    Parametro RIORGANIZZARE, utilizzato per riorganizzare le pagine dei nodi indice;

    Parametro DISABILITA, utilizzato per disabilitare l'indice. Queste tre opzioni vengono discusse nelle sottosezioni seguenti.

Ricostruire l'indice

Qualsiasi modifica ai dati utilizzando le istruzioni INSERT, UPDATE o DELETE può comportare la frammentazione dei dati. Se questi dati vengono indicizzati, è possibile anche la frammentazione dell'indice, con le informazioni dell'indice sparse su diverse pagine fisiche. A causa della frammentazione dei dati dell'indice, il Motore di database potrebbe essere costretto a eseguire ulteriori operazioni di lettura dei dati, riducendo le prestazioni complessive del sistema. In questo caso, è necessario RICOSTRUIRE tutti gli indici frammentati.

Questo può essere fatto in due modi:

    tramite il parametro REBUILD dell'istruzione ALTER INDEX;

    tramite il parametro DROP_EXISTING dell'istruzione CREATE INDEX.

Il parametro REBUILD viene utilizzato per ricostruire gli indici. Se si specifica ALL invece del nome dell'indice per questo parametro, verranno ricreati tutti gli indici sulla tabella. (Consentendo la ricreazione dinamica degli indici, non sarà necessario rilasciarli e ricrearli.)

L'opzione DROP_EXISTING dell'istruzione CREATE INDEX può migliorare le prestazioni quando si ricrea un indice cluster su una tabella che contiene anche indici non cluster. Specifica che un indice cluster o non cluster esistente deve essere eliminato e l'indice specificato deve essere ricreato. Come accennato in precedenza, ogni indice non cluster su una tabella cluster contiene nei suoi nodi dell'albero i valori corrispondenti dell'indice cluster della tabella. Per questo motivo, quando si rilascia un indice cluster su una tabella, è necessario ricreare tutti i relativi indici non cluster. L'utilizzo del parametro DROP_EXISTING evita di dover ricreare nuovamente gli indici non cluster.

L'opzione DROP_EXISTING è più potente dell'opzione REBUILD perché è più flessibile e fornisce diverse opzioni, come la modifica delle colonne che compongono l'indice e la modifica di un indice non cluster in uno cluster.

Riorganizzazione delle pagine dei nodi indice

Il parametro REORGANIZE dell'istruzione ALTER INDEX riorganizza le pagine dei nodi nell'indice specificato in modo che l'ordine fisico delle pagine corrisponda al loro ordine logico, da sinistra a destra. Ciò rimuove una certa quantità di frammentazione dell'indice, migliorando le prestazioni dell'indice.

Disabilita indice

L'opzione DISABLE disabilita l'indice specificato. Un indice disabilitato non è disponibile per l'uso finché non viene nuovamente abilitato. Tieni presente che un indice disabilitato non cambia quando vengono apportate modifiche ai dati associati. Per questo motivo, per riutilizzare un indice disabilitato, è necessario ricrearlo completamente. Per abilitare un indice disabilitato, utilizzare l'opzione REBUILD dell'istruzione ALTER TABLE.

Quando un indice cluster su una tabella è disabilitato, i dati della tabella non saranno accessibili perché tutte le pagine di dati della tabella con l'indice cluster vengono archiviate nei relativi nodi dell'albero.

Rimozione e ridenominazione degli indici

Per rimuovere gli indici nel database corrente, utilizzare Istruzione DROP INDEX. Tieni presente che l'eliminazione di un indice cluster su una tabella può essere un'operazione che richiede molte risorse perché Tutti gli indici non cluster dovranno essere ricreati. (Tutti gli indici non cluster utilizzano la chiave dell'indice cluster come puntatore nelle pagine del nodo.) L'utilizzo dell'istruzione DROP INDEX per eliminare un indice è illustrato nell'esempio seguente:

USA CampioneDb; DROP INDEX ix_empid ON Dipendente;

L'istruzione DROP INDEX ha un'istruzione aggiuntiva parametro SPOSTA A, il cui significato è lo stesso del parametro ON dell'istruzione CREATE INDEX. In altre parole, è possibile utilizzare questo parametro per specificare dove spostare le righe di dati presenti nelle pagine del nodo dell'indice cluster. I dati vengono spostati in una nuova posizione come heap. È possibile specificare un filegroup predefinito o un filegroup denominato per la nuova posizione di archiviazione dei dati.

L'istruzione DROP INDEX non può essere utilizzata per eliminare gli indici creati implicitamente dal sistema per vincoli di integrità, come gli indici PRIMARY KEY e UNIQUE. Per rimuovere tali indici, è necessario rimuovere il vincolo corrispondente.

Gli indici possono essere rinominati utilizzando la procedura di sistema sp_rename.

Gli indici possono anche essere creati, modificati ed eliminati in Management Studio utilizzando diagrammi di database o Esplora oggetti. Ma il modo più semplice è utilizzare la cartella Indici della tabella richiesta. La gestione degli indici in Management Studio è simile alla gestione delle tabelle in Management Studio.

Anche se il Motore di database non pone alcun limite pratico al numero di indici, esistono un paio di motivi per cui dovresti limitarne il numero. Innanzitutto, ciascun indice occupa una certa quantità di spazio su disco, quindi esiste la possibilità che il numero totale di pagine di indice del database superi il numero di pagine di dati nel database. In secondo luogo, a differenza del vantaggio derivante dall’utilizzo di un indice per recuperare dati, l’inserimento e l’eliminazione dei dati non fornisce tale vantaggio a causa della necessità di mantenere l’indice. Maggiore è il numero di indici di una tabella, maggiore sarà il lavoro necessario per riorganizzarli. Come regola generale, è saggio selezionare gli indici per le query frequenti e quindi valutarne l'utilizzo.

In questa sezione vengono fornite alcune linee guida per la creazione e l'utilizzo degli indici. Le seguenti raccomandazioni sono solo regole generali. In definitiva, la loro efficacia dipenderà da come viene utilizzato nella pratica il database e dal tipo di query eseguite più frequentemente. Indicizzare una colonna che non verrà mai utilizzata non servirà a nulla.

Indici e condizioni delle clausole WHERE

Se la clausola WHERE di un'istruzione SELECT contiene una condizione di ricerca con una colonna, è necessario creare un indice su quella colonna. Ciò è particolarmente consigliato in condizioni di elevata selettività. Per selettività di una condizione intendiamo il rapporto tra il numero di righe che soddisfano la condizione e il numero totale di righe nella tabella. Ad un'elevata selettività corrisponde un valore più basso di questo rapporto. L'elaborazione della ricerca utilizzando una colonna indicizzata avrà più successo quando la selettività della condizione è inferiore al 5%.

Una colonna non deve essere indicizzata se il livello di selettività della condizione è costante pari o superiore all'80%. In questo caso, le pagine indice richiederanno ulteriori operazioni di I/O, che ridurranno il risparmio di tempo ottenuto utilizzando gli indici. In questo caso, è più veloce eseguire una ricerca scansionando la tabella, che è ciò che solitamente sceglierà il Query Optimizer, rendendo l'indice inutilizzabile.

Se la condizione di ricerca di una query utilizzata di frequente contiene operatori AND, la soluzione migliore è creare un indice composto su tutte le colonne della tabella specificate nella clausola WHERE dell'istruzione SELECT. La creazione di un tale indice composito è mostrata nell'esempio seguente:

Questo esempio crea un indice composito su tutte le colonne della clausola WHERE. In questa query, due condizioni sono combinate con AND, quindi dovresti creare un indice composto non cluster su entrambe le colonne in tali condizioni.

Indici e operatore di join

Per un'operazione di unione, è consigliabile creare un indice su ciascuna colonna da unire. Le colonne unite spesso rappresentano la chiave primaria di una tabella e la corrispondente chiave esterna di un'altra tabella. Se specifichi i vincoli di integrità PRIMARY KEY e FOREIGN KEY sulle colonne di join corrispondenti, dovresti creare solo un indice non cluster sulla colonna della chiave esterna perché il sistema creerà implicitamente un indice cluster sulla colonna della chiave primaria.

L'esempio seguente mostra come creare gli indici che verrebbero utilizzati se si disponesse di una query con un'operazione di unione e un filtro aggiuntivo:

Indice di copertura

Come accennato in precedenza, l'inclusione di tutte le colonne di query in un indice può migliorare significativamente le prestazioni delle query. La creazione di tale indice, chiamato copertura, è mostrata nell'esempio seguente:

USA AdventureWorks2012; GO DROP INDEX Persona.Indirizzo.IX_Indirizzo_StatoProvinceID; GO CREATE INDEX ix_address_zip ON Persona.Indirizzo(Codice Postale) INCLUDE(Città, StatoProvincia); GO SELECT Città, StatoProvinciaID FROM Persona.Indirizzo WHERE Codice Postale = 84407;

In questo esempio viene innanzitutto rimosso l'indice IX_Address_StateProvinceID dalla tabella degli indirizzi. Viene quindi creato un nuovo indice che include due colonne aggiuntive oltre alla colonna PostalCode. Infine, l'istruzione SELECT alla fine dell'esempio mostra la query coperta dall'indice. Per questa query, il sistema non ha bisogno di cercare i dati nelle pagine di dati perché Query Optimizer può trovare tutti i valori di colonna nelle pagine dei nodi di indice non cluster.

Gli indici di copertura sono consigliati poiché le pagine indice in genere contengono molte più voci rispetto alle pagine dati corrispondenti. Inoltre, per utilizzare questo metodo, le colonne da filtrare devono essere le prime colonne chiave nell'indice.

Indici su colonne calcolate

Il Motore di database consente di creare i seguenti tipi speciali di indici:

    viste indicizzate;

    indici filtrabili;

    indici su colonne calcolate;

    indici partizionati;

    indici di persistenza delle colonne;

    Indici XML;

    indici integrali.

In questa sezione vengono illustrate le colonne calcolate e i relativi indici associati.

Colonna calcolataè una colonna della tabella in cui vengono archiviati i risultati dei calcoli dei dati della tabella. Tale colonna può essere virtuale o persistente. Questi due tipi di colonne vengono discussi nelle sottosezioni seguenti.

Colonne calcolate virtuali

Una colonna calcolata che non ha un indice cluster corrispondente è una colonna logica, ovvero non è fisicamente archiviato sul disco rigido. Pertanto, viene valutato ogni volta che si accede alla riga. L'uso delle colonne calcolate virtuali è mostrato nell'esempio seguente:

USA CampioneDb; CREATE TABLE Ordini (OrderId INT NOT NULL, Prezzo MONEY NOT NULL, Quantità INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Prezzo * Quantità, SpeditoDate AS DATEADD (DAY, 7, orderdate));

La tabella Ordini in questo esempio ha due colonne calcolate virtuali: totale e data spedizione. La colonna del totale viene calcolata utilizzando altre due colonne, prezzo e quantità, mentre la colonna della data di spedizione viene calcolata utilizzando la funzione DATEADD e la colonna della data dell'ordine.

Colonne calcolate costanti

Il Motore di database consente di creare indici su colonne calcolate deterministiche in cui le colonne sottostanti hanno tipi di dati precisi. (Una colonna calcolata si dice deterministica se restituisce sempre gli stessi valori per gli stessi dati della tabella.)

Una colonna calcolata indicizzata può essere creata solo se i seguenti parametri dell'istruzione SET sono impostati su ON (questi parametri garantiscono che la colonna sia deterministica):

    QUOTED_IDENTIFICATORE

    CONCAT_NULL_YIELDS_NULL

Inoltre, il parametro NUMERIC_ROUNDABORT deve essere impostato su off.

Se crei un indice cluster su una colonna calcolata, i valori della colonna esisteranno fisicamente nelle righe della tabella corrispondenti perché le pagine del nodo dell'indice cluster contengono le righe di dati. L'esempio seguente crea un indice cluster sul totale di una colonna calcolata dalla tabella Orders:

USA CampioneDb; CREA INDICE CLUSTERED ix1 ON Ordini (totale);

Dopo aver eseguito l'istruzione CREATE INDEX, la colonna totale calcolata sarà fisicamente presente nella tabella. Ciò significa che tutti gli aggiornamenti alle colonne sottostanti di una colonna calcolata ne causeranno l'aggiornamento.

La colonna può essere resa costante in un altro modo utilizzando Parametro PERSISTITO. Questa opzione consente di specificare la presenza fisica di una colonna calcolata senza nemmeno creare un indice cluster corrispondente. Questa funzionalità è necessaria per creare colonne fisiche calcolate, che vengono create su colonne con un tipo di dati approssimativo (float o reale). (Come accennato in precedenza, è possibile creare un indice su una colonna calcolata solo se le colonne sottostanti sono dello stesso tipo di dati.)

Indici- questa è la prima cosa che devi capire bene nel tuo lavoro server SQL, ma stranamente le domande basilari non vengono poste molto spesso sui forum e non ricevono molte risposte.
Rob Sheldon risponde a queste domande che causano confusione negli ambienti professionali sugli indici in server SQL: ad alcuni di essi siamo semplicemente imbarazzati a chiedere, ma prima di chiedere ad altri ci penseremo due volte.

Terminologia utilizzata:

indice indice
mucchio un mucchio
tavolo tavolo
visualizzazione prestazione
B-albero albero equilibrato
indice cluster indice cluster
indice non cluster indice non cluster
indice composito indice composito
indice di copertura indice di copertura
vincolo di chiave primaria vincolo di chiave primaria
vincolo unico restrizione sull’unicità dei valori
domanda richiesta
motore di interrogazione sottosistema di interrogazione
Banca dati Banca dati
motore di base di dati sottosistema di archiviazione
fattore di riempimento fattore di riempimento dell'indice
chiave primaria surrogata chiave primaria surrogata
ottimizzatore di query ottimizzatore di query
selettività dell'indice selettività dell'indice
indice filtrato indice filtrabile
progetto esecutivo progetto esecutivo

Nozioni di base sugli indici in SQL Server.

Uno dei modi più importanti per ottenere un'elevata produttività server SQLè l'uso degli indici. Un indice accelera il processo di query fornendo un accesso rapido alle righe di dati in una tabella, proprio come un indice in un libro ti aiuta a trovare rapidamente le informazioni di cui hai bisogno. In questo articolo fornirò una breve panoramica degli indici in server SQL e spiegare come sono organizzati nel database e come aiutano a velocizzare le query del database.

Gli indici vengono creati sulle colonne della tabella e della vista. Gli indici forniscono un modo per cercare rapidamente i dati in base ai valori in tali colonne. Ad esempio, se crei un indice su una chiave primaria e quindi cerchi una riga di dati utilizzando i valori della chiave primaria, allora server SQL troverà prima il valore dell'indice e quindi utilizzerà l'indice per trovare rapidamente l'intera riga di dati. Senza un indice, verrà eseguita una scansione completa di tutte le righe della tabella, il che può avere un impatto significativo sulle prestazioni.
Puoi creare un indice sulla maggior parte delle colonne in una tabella o vista. L'eccezione riguarda principalmente le colonne con tipi di dati per l'archiviazione di oggetti di grandi dimensioni ( PALLONETTO), ad esempio Immagine, testo O varchar(massimo). Puoi anche creare indici su colonne progettate per archiviare dati nel formato XML, ma questi indici sono strutturati in modo leggermente diverso rispetto a quelli standard e la loro considerazione va oltre lo scopo di questo articolo. Inoltre, l'articolo non discute columnstore indici. Mi concentrerò invece sugli indici più comunemente utilizzati nei database server SQL.
Un indice è costituito da un insieme di pagine, nodi indice, che sono organizzate in una struttura ad albero - albero equilibrato. Questa struttura è di natura gerarchica e inizia con un nodo radice in cima alla gerarchia e nodi foglia, le foglie, in basso, come mostrato nella figura:

Quando esegui una query su una colonna indicizzata, il motore di query inizia dalla parte superiore del nodo radice e procede verso il basso attraverso i nodi intermedi, con ogni livello intermedio contenente informazioni più dettagliate sui dati. Il motore di query continua a spostarsi attraverso i nodi dell'indice finché non raggiunge il livello inferiore con le foglie dell'indice. Ad esempio, se stai cercando il valore 123 in una colonna indicizzata, il motore di query determinerà innanzitutto la pagina al primo livello intermedio a livello radice. In questo caso, la prima pagina punta ad un valore da 1 a 100, e la seconda da 101 a 200, quindi il motore di query accederà alla seconda pagina di questo livello intermedio. Successivamente vedrai che dovresti passare alla terza pagina del livello intermedio successivo. Da qui il sottosistema di query leggerà il valore dell'indice stesso ad un livello inferiore. Le foglie dell'indice possono contenere i dati della tabella stessa o semplicemente un puntatore alle righe con i dati nella tabella, a seconda del tipo di indice: indice cluster o indice non cluster.

Indice cluster

Un indice cluster archivia le righe di dati effettive nelle foglie dell'indice. Tornando all'esempio precedente, ciò significa che la riga di dati associata al valore della chiave 123 verrà memorizzata nell'indice stesso. Una caratteristica importante di un indice cluster è che tutti i valori sono ordinati in un ordine specifico, ascendente o discendente. Pertanto, una tabella o una vista può avere un solo indice cluster. Inoltre, va notato che i dati in una tabella vengono archiviati in forma ordinata solo se su questa tabella è stato creato un indice cluster.
Una tabella che non ha un indice cluster è chiamata heap.

Indice non cluster

A differenza di un indice cluster, le foglie di un indice non cluster contengono solo le colonne ( chiave) con cui viene determinato questo indice e contiene anche un puntatore alle righe con dati reali nella tabella. Ciò significa che il sistema di sottoquery richiede un'operazione aggiuntiva per individuare e recuperare i dati richiesti. Il contenuto del puntatore dati dipende da come vengono archiviati i dati: tabella cluster o heap. Se un puntatore punta a una tabella cluster, punta a un indice cluster che può essere utilizzato per trovare i dati effettivi. Se un puntatore fa riferimento a un heap, punta a un identificatore di riga di dati specifico. Gli indici non cluster non possono essere ordinati come gli indici cluster, ma puoi creare più di un indice non cluster su una tabella o vista, fino a 999. Ciò non significa che dovresti creare il maggior numero possibile di indici. Gli indici possono migliorare o peggiorare le prestazioni del sistema. Oltre a poter creare più indici non cluster, puoi anche includere colonne aggiuntive ( colonna inclusa) nel suo indice: le foglie dell'indice memorizzeranno non solo il valore delle colonne indicizzate stesse, ma anche i valori di queste colonne aggiuntive non indicizzate. Questo approccio ti consentirà di aggirare alcune delle restrizioni imposte all'indice. Ad esempio, puoi includere una colonna non indicizzabile o ignorare il limite di lunghezza dell'indice (900 byte nella maggior parte dei casi).

Tipi di indici

Oltre ad essere un indice cluster o non cluster, può essere ulteriormente configurato come indice composito, indice univoco o indice di copertura.

Indice composito

Un indice di questo tipo può contenere più di una colonna. Puoi includere fino a 16 colonne in un indice, ma la loro lunghezza totale è limitata a 900 byte. Sia gli indici cluster che quelli non cluster possono essere compositi.

Indice univoco

Questo indice garantisce che ogni valore nella colonna indicizzata sia univoco. Se l'indice è composito, l'univocità si applica a tutte le colonne dell'indice, ma non a ogni singola colonna. Ad esempio, se crei un indice univoco sulle colonne NOME E COGNOME, il nome completo deve essere univoco, ma sono possibili duplicati nel nome o nel cognome.
Un indice univoco viene creato automaticamente quando definisci un vincolo di colonna: vincolo di chiave primaria o valore univoco:

  • Chiave primaria
    Quando definisci un vincolo di chiave primaria su una o più colonne, allora server SQL crea automaticamente un indice cluster univoco se non è stato creato un indice cluster in precedenza (in questo caso viene creato un indice univoco non cluster sulla chiave primaria)
  • Unicità dei valori
    Quando si definisce un vincolo sull’unicità dei valori allora server SQL crea automaticamente un indice univoco non cluster. È possibile specificare che venga creato un indice cluster univoco se nella tabella non è stato ancora creato alcun indice cluster
Indice di copertura

Un tale indice consente a una query specifica di ottenere immediatamente tutti i dati necessari dalle foglie dell'indice senza ulteriore accesso ai record della tabella stessa.

Progettare indici

Per quanto utili possano essere gli indici, devono essere progettati con attenzione. Poiché gli indici possono occupare molto spazio su disco, non è consigliabile crearne più del necessario. Inoltre, gli indici vengono aggiornati automaticamente quando viene aggiornata la riga di dati stessa, il che può comportare un ulteriore sovraccarico delle risorse e un degrado delle prestazioni. Quando si progettano gli indici, è necessario tenere conto di diverse considerazioni relative al database e alle query su di esso.

Banca dati

Come notato in precedenza, gli indici possono migliorare le prestazioni del sistema perché forniscono al motore di query un modo rapido per trovare i dati. Tuttavia, dovresti tenere conto anche della frequenza con cui intendi inserire, aggiornare o eliminare i dati. Quando si modificano i dati, è necessario modificare anche gli indici per riflettere le azioni corrispondenti sui dati, il che può ridurre significativamente le prestazioni del sistema. Considera le seguenti linee guida quando pianifichi la tua strategia di indicizzazione:

  • Per le tabelle aggiornate frequentemente, utilizzare il minor numero possibile di indici.
  • Se la tabella contiene una grande quantità di dati ma le modifiche sono minori, utilizza tutti gli indici necessari per migliorare le prestazioni delle query. Tuttavia, pensa attentamente prima di utilizzare gli indici su tabelle di piccole dimensioni, perché... È possibile che l'utilizzo di una ricerca per indice richieda più tempo rispetto alla semplice scansione di tutte le righe.
  • Per gli indici cluster, prova a mantenere i campi il più brevi possibile. L'approccio migliore è utilizzare un indice cluster su colonne che hanno valori univoci e non consentono NULL. Questo è il motivo per cui una chiave primaria viene spesso utilizzata come indice cluster.
  • L'unicità dei valori in una colonna influisce sulle prestazioni dell'indice. In generale, maggiore è il numero di duplicati presenti in una colonna, peggiore sarà il rendimento dell'indice. D’altra parte, più valori sono unici, migliore sarà la performance dell’indice. Utilizzare un indice univoco quando possibile.
  • Per un indice composito, prendere in considerazione l'ordine delle colonne nell'indice. Colonne utilizzate nelle espressioni DOVE(Per esempio, DOVE Nome = 'Charlie') deve essere il primo nell'indice. Le colonne successive dovrebbero essere elencate in base all'unicità dei loro valori (le colonne con il maggior numero di valori univoci vengono per prime).
  • Puoi anche specificare un indice sulle colonne calcolate se soddisfano determinati requisiti. Ad esempio, le espressioni utilizzate per ottenere il valore di una colonna devono essere deterministiche (restituiscono sempre lo stesso risultato per un determinato insieme di parametri di input).
Interrogazioni del database

Un'altra considerazione da tenere in considerazione quando si progettano gli indici è quali query vengono eseguite sul database. Come affermato in precedenza, è necessario considerare la frequenza con cui i dati cambiano. Inoltre, dovrebbero essere utilizzati i seguenti principi:

  • Prova a inserire o modificare quante più righe possibile in una query, anziché farlo in più query singole.
  • Crea un indice non cluster sulle colonne utilizzate di frequente come termini di ricerca nelle tue query. DOVE e connessioni in GIUNTURA.
  • Prendi in considerazione l'indicizzazione delle colonne utilizzate nelle query di ricerca di righe per corrispondenze di valori esatti.

Perché una tabella non può avere due indici cluster?

Vuoi una risposta breve? Un indice cluster è una tabella. Quando crei un indice cluster su una tabella, il motore di archiviazione ordina tutte le righe nella tabella in ordine crescente o decrescente, in base alla definizione dell'indice. Un indice cluster non è un'entità separata come gli altri indici, ma un meccanismo per ordinare i dati in una tabella e facilitare l'accesso rapido alle righe di dati.
Immaginiamo di avere una tabella contenente la cronologia delle transazioni di vendita. La tabella Vendite include informazioni come ID ordine, posizione del prodotto nell'ordine, numero prodotto, quantità di prodotto, numero e data dell'ordine, ecc. Crei un indice cluster su colonne ID ordine E ID linea, ordinati in ordine crescente come mostrato di seguito T-SQL codice:

CREA INDICE CLUSTER UNICO ix_oririded_lineid ON dbo.Sales(OrderID, LineID);

Quando esegui questo script, tutte le righe nella tabella verranno ordinate fisicamente prima in base alla colonna OrderID e poi in base a LineID, ma i dati stessi rimarranno in un singolo blocco logico, la tabella. Per questo motivo non è possibile creare due indici cluster. Può esserci solo una tabella con un dato e quella tabella può essere ordinata solo una volta in un ordine specifico.

Se una tabella in cluster offre molti vantaggi, perché utilizzare un heap?

Hai ragione. Le tabelle cluster sono ottime e la maggior parte delle query funzionerà meglio su tabelle con un indice cluster. Ma in alcuni casi potresti voler lasciare i tavoli nel loro stato naturale e incontaminato, ad es. sotto forma di heap e crea solo indici non cluster per mantenere in esecuzione le query.
L'heap, come ricorderete, memorizza i dati in ordine casuale. In genere, il sottosistema di archiviazione aggiunge dati a una tabella nell'ordine in cui vengono inseriti, ma preferisce anche spostare le righe per un'archiviazione più efficiente. Di conseguenza, non hai alcuna possibilità di prevedere in quale ordine verranno archiviati i dati.
Se il motore delle query deve trovare dati senza il vantaggio di un indice non cluster, eseguirà una scansione completa della tabella per trovare le righe necessarie. Su tabelle molto piccole questo di solito non è un problema, ma man mano che l'heap aumenta di dimensioni, le prestazioni diminuiscono rapidamente. Naturalmente, un indice non cluster può essere d'aiuto utilizzando un puntatore al file, alla pagina e alla riga in cui sono archiviati i dati richiesti: di solito è un'alternativa molto migliore alla scansione di una tabella. Anche così, è difficile confrontare i vantaggi di un indice cluster quando si considerano le prestazioni delle query.
Tuttavia, l'heap può contribuire a migliorare le prestazioni in determinate situazioni. Considera una tabella con molti inserimenti ma pochi aggiornamenti o eliminazioni. Ad esempio, una tabella che memorizza un registro viene utilizzata principalmente per inserire valori fino all'archiviazione. Nell'heap non vedrai il paging e la frammentazione dei dati come faresti con un indice cluster perché le righe vengono semplicemente aggiunte alla fine dell'heap. Dividere troppo le pagine può avere un impatto significativo sulle prestazioni, e non in senso positivo. In generale, l'heap consente di inserire i dati in modo relativamente semplice e non sarà necessario gestire i costi di archiviazione e manutenzione che avresti con un indice cluster.
Ma la mancanza di aggiornamento e cancellazione dei dati non dovrebbe essere considerata l’unica ragione. Anche il modo in cui i dati vengono campionati è un fattore importante. Ad esempio, non dovresti utilizzare un heap se esegui frequentemente query su intervalli di dati o se i dati su cui esegui query spesso devono essere ordinati o raggruppati.
Tutto ciò significa che dovresti considerare l'utilizzo dell'heap solo quando lavori con tabelle molto piccole o tutta la tua interazione con la tabella è limitata all'inserimento di dati e le tue query sono estremamente semplici (e stai utilizzando indici non cluster Comunque). Altrimenti, mantieni un indice cluster ben progettato, come uno definito su un semplice campo chiave ascendente, come una colonna ampiamente utilizzata con IDENTITÀ.

Come posso modificare il fattore di riempimento dell'indice predefinito?

La modifica del fattore di riempimento dell'indice predefinito è una cosa. Capire come funziona il rapporto di default è un'altra questione. Ma prima, fai qualche passo indietro. Il fattore di riempimento dell'indice determina la quantità di spazio sulla pagina per memorizzare l'indice al livello inferiore (livello foglia) prima di iniziare a riempire una nuova pagina. Ad esempio, se il coefficiente è impostato su 90, quando l'indice cresce, occuperà il 90% della pagina e poi passerà alla pagina successiva.
Per impostazione predefinita, il valore del fattore di riempimento dell'indice è presente server SQLè 0, che equivale a 100. Di conseguenza, tutti i nuovi indici ereditano automaticamente questa impostazione a meno che non si specifichi specificamente un valore nel codice diverso dal valore standard di sistema o si modifichi il comportamento predefinito. Puoi usare Studio di gestione di SQL Server per modificare il valore predefinito o eseguire una procedura memorizzata di sistema sp_configure. Ad esempio, il seguente set T-SQL imposta il valore del coefficiente su 90 (è necessario prima passare alla modalità impostazioni avanzate):

EXEC sp_configure "mostra opzioni avanzate ", 1; VAI RICONFIGURA; VAI EXEC sp_configure " fattore di riempimento", 90; VAI RICONFIGURA; VAI

Dopo aver modificato il valore del fattore di riempimento dell'indice, è necessario riavviare il servizio server SQL. Ora puoi controllare il valore impostato eseguendo sp_configure senza il secondo argomento specificato:

EXEC sp_configure "fattore di riempimento" GO

Questo comando dovrebbe restituire un valore pari a 90. Di conseguenza, tutti gli indici appena creati utilizzeranno questo valore. Puoi verificarlo creando un indice ed effettuando una query per il valore del fattore di riempimento:

USA AdventureWorks2012; - il tuo database VAI CREARE INDICE NON CLUSTERED ix_people_lastname ON Persona.Persona(Cognome); GO SELECT fill_factor FROM sys .indexes DOVE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;

In questo esempio, abbiamo creato un indice non cluster su una tabella Persona nella banca dati AvventuraWorks2012. Dopo aver creato l'indice, possiamo ottenere il valore del fattore di riempimento dalle tabelle di sistema sys.indexes. La query dovrebbe restituire 90.
Tuttavia, immaginiamo di aver eliminato l'indice e di averlo creato di nuovo, ma ora abbiamo specificato un valore specifico per il fattore di riempimento:

CREA INDICE NON CLUSTERED ix_people_lastname ON Persona.Persona(Cognome) WITH (fillfactor=80 ); GO SELECT fill_factor FROM sys .indexes DOVE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;

Questa volta abbiamo aggiunto le istruzioni CON e opzione fillfactor per la nostra operazione di creazione dell'indice CREA INDICE e specificato il valore 80. Operatore SELEZIONARE ora restituisce il valore corrispondente.
Finora tutto è stato abbastanza semplice. Il punto in cui puoi davvero bruciarti in tutto questo processo è quando crei un indice che utilizza un valore di coefficiente predefinito, presupponendo che tu conosca quel valore. Ad esempio, qualcuno sta armeggiando con le impostazioni del server ed è così testardo da impostare il fattore di riempimento dell'indice su 20. Nel frattempo, continui a creare indici, presupponendo che il valore predefinito sia 0. Sfortunatamente, non hai modo di scoprire il riempimento fattore fino a quando non crei un indice e poi controlli il valore come abbiamo fatto nei nostri esempi. Altrimenti, dovrai aspettare il momento in cui le prestazioni delle query diminuiranno così tanto da iniziare a sospettare qualcosa.
Un altro problema di cui dovresti essere consapevole è la ricostruzione degli indici. Come per la creazione di un indice, puoi specificare il valore del fattore di riempimento dell'indice quando lo ricostruisci. Tuttavia, a differenza del comando create indice, la ricostruzione non utilizza le impostazioni predefinite del server, nonostante ciò che potrebbe sembrare. Ancor di più, se non si specifica specificamente il valore del fattore di riempimento dell'indice, allora server SQL utilizzerà il valore del coefficiente con cui esisteva questo indice prima della sua ristrutturazione. Ad esempio, la seguente operazione ALTERARE L'INDICE ricostruisce l'indice che abbiamo appena creato:

ALTER INDEX ix_people_lastname ON Persona.Persona REBUILD ; GO SELECT fill_factor FROM sys .indexes DOVE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;

Quando controlliamo il valore del fattore di riempimento, otterremo un valore pari a 80, perché è quello che abbiamo specificato l'ultima volta che abbiamo creato l'indice. Il valore predefinito viene ignorato.
Come puoi vedere, modificare il valore del fattore di riempimento dell'indice non è così difficile. Molto più difficile è conoscere il valore attuale e capire quando viene applicato. Se specifichi sempre in modo specifico il coefficiente durante la creazione e la ricostruzione degli indici, conoscerai sempre il risultato specifico. A meno che tu non debba preoccuparti di assicurarti che qualcun altro non rovini nuovamente le impostazioni del server, causando la ricostruzione di tutti gli indici con un fattore di riempimento dell'indice ridicolmente basso.

È possibile creare un indice cluster su una colonna che contiene duplicati?

Sì e no. Sì, puoi creare un indice cluster su una colonna chiave che contiene valori duplicati. No, il valore di una colonna chiave non può rimanere in uno stato non univoco. Lasciatemi spiegare. Se crei un indice cluster non univoco su una colonna, il motore di archiviazione aggiunge un univoco al valore duplicato per garantire l'unicità e quindi essere in grado di identificare ogni riga nella tabella cluster.
Ad esempio, potresti decidere di creare un indice cluster su una colonna contenente i dati dei clienti Cognome mantenendo il cognome. La colonna contiene i valori Franklin, Hancock, Washington e Smith. Quindi inserisci nuovamente i valori Adams, Hancock, Smith e Smith. Ma il valore della colonna chiave deve essere univoco, quindi il motore di archiviazione modificherà il valore dei duplicati in modo che assomiglino a questo: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 e Smith5678.
A prima vista, questo approccio sembra valido, ma un valore intero aumenta la dimensione della chiave, il che può diventare un problema se è presente un numero elevato di duplicati e questi valori diventeranno la base di un indice non cluster o di un indice estraneo. riferimento chiave. Per questi motivi, dovresti sempre provare a creare indici cluster univoci quando possibile. Se ciò non è possibile, prova almeno a utilizzare colonne con un contenuto dal valore univoco molto elevato.

Come viene archiviata la tabella se non è stato creato un indice cluster?

server SQL supporta due tipi di tabelle: tabelle cluster che hanno un indice cluster e tabelle heap o solo heap. A differenza delle tabelle raggruppate, i dati nell'heap non vengono ordinati in alcun modo. In sostanza, questa è una pila (mucchio) di dati. Se aggiungi una riga a tale tabella, il motore di archiviazione la aggiungerà semplicemente alla fine della pagina. Quando la pagina è piena di dati, verrà aggiunta a una nuova pagina. Nella maggior parte dei casi, ti consigliamo di creare un indice cluster su una tabella per sfruttare l'ordinabilità e la velocità delle query (prova a immaginare di cercare un numero di telefono in una rubrica non ordinata). Tuttavia, se si sceglie di non creare un indice cluster, è comunque possibile creare un indice non cluster nell'heap. In questo caso, ogni riga dell'indice avrà un puntatore a una riga dell'heap. L'indice include l'ID del file, il numero di pagina e il numero della riga dati.

Qual è la relazione tra i vincoli di unicità del valore e una chiave primaria con gli indici di tabella?

Una chiave primaria e un vincolo univoco assicurano che i valori in una colonna siano univoci. Puoi creare solo una chiave primaria per una tabella e non può contenere valori NULLO. È possibile creare diverse restrizioni sull'unicità di un valore per una tabella e ciascuna di esse può avere un singolo record NULLO.
Quando crei una chiave primaria, il motore di archiviazione crea anche un indice cluster univoco se non è già stato creato un indice cluster. Tuttavia, puoi ignorare il comportamento predefinito e verrà creato un indice non cluster. Se esiste un indice cluster quando crei la chiave primaria, verrà creato un indice non cluster univoco.
Quando crei un vincolo univoco, il motore di archiviazione crea un indice univoco e non cluster. Tuttavia, è possibile specificare la creazione di un indice cluster univoco se non ne è stato creato uno in precedenza.
In generale, un vincolo di valore univoco e un indice univoco sono la stessa cosa.

Perché gli indici cluster e non cluster sono chiamati albero B in SQL Server?

Gli indici di base in SQL Server, in cluster o non in cluster, sono distribuiti su set di pagine detti nodi indice. Queste pagine sono organizzate in una gerarchia specifica con una struttura ad albero chiamata albero bilanciato. Al livello superiore c'è il nodo radice, in basso ci sono i nodi foglia, con nodi intermedi tra il livello superiore e quello inferiore, come mostrato in figura:

Il nodo radice fornisce il punto di ingresso principale per le query che tentano di recuperare i dati tramite l'indice. A partire da questo nodo, il motore di query avvia una navigazione lungo la struttura gerarchica fino al nodo foglia appropriato contenente i dati.
Ad esempio, immaginiamo che sia stata ricevuta una richiesta per selezionare righe contenenti un valore chiave pari a 82. Il sottosistema di query inizia a funzionare dal nodo radice, che fa riferimento a un nodo intermedio adatto, nel nostro caso 1-100. Dal nodo intermedio 1-100 si passa al nodo 51-100 e da lì al nodo finale 76-100. Se si tratta di un indice cluster, la foglia del nodo contiene i dati della riga associata alla chiave uguale a 82. Se si tratta di un indice non cluster, la foglia dell'indice contiene un puntatore alla tabella cluster o una riga specifica in il mucchio.

Come può un indice migliorare le prestazioni delle query se è necessario attraversare tutti questi nodi dell'indice?

Innanzitutto, gli indici non sempre migliorano le prestazioni. Troppi indici creati in modo errato trasformano il sistema in un pantano e riducono le prestazioni delle query. È più accurato affermare che, se gli indici vengono applicati con attenzione, possono fornire miglioramenti significativi in ​​termini di prestazioni.
Pensa a un enorme libro dedicato all'ottimizzazione delle prestazioni server SQL(versione cartacea, non elettronica). Immagina di voler trovare informazioni sulla configurazione di Resource Governor. Puoi trascinare il dito pagina per pagina attraverso l'intero libro, oppure aprire il sommario e scoprire il numero esatto della pagina con le informazioni che stai cercando (a condizione che il libro sia indicizzato correttamente e che i contenuti abbiano gli indici corretti). Ciò ti farà sicuramente risparmiare molto tempo, anche se dovrai prima accedere a una struttura completamente diversa (l'indice) per ottenere le informazioni di cui hai bisogno dalla struttura primaria (il libro).
Come l'indice di un libro, un indice in server SQL ti consente di eseguire query precise sui dati di cui hai bisogno invece di scansionare completamente tutti i dati contenuti in una tabella. Per le tabelle di piccole dimensioni, una scansione completa in genere non costituisce un problema, ma le tabelle di grandi dimensioni occupano molte pagine di dati, il che può comportare un notevole tempo di esecuzione delle query a meno che non esista un indice per consentire al motore delle query di ottenere immediatamente la posizione corretta dei dati. Immagina di perderti in un incrocio stradale a più livelli di fronte a una grande metropoli senza una mappa e avrai un'idea.

Se gli indici sono così fantastici, perché non crearne uno su ogni colonna?

Nessuna buona azione dovrebbe rimanere impunita. Almeno questo è il caso degli indici. Naturalmente, gli indici funzionano perfettamente finché si eseguono query di recupero dell'operatore SELEZIONARE, ma non appena iniziano le chiamate frequenti agli operatori INSERIRE, AGGIORNAMENTO E ELIMINARE, quindi il paesaggio cambia molto rapidamente.
Quando si avvia una richiesta di dati da parte dell'operatore SELEZIONARE, il motore di query trova l'indice, si sposta nella struttura ad albero e scopre i dati che sta cercando. Cosa potrebbe essere più semplice? Ma le cose cambiano se avvii una dichiarazione di cambiamento come AGGIORNAMENTO. Sì, per la prima parte dell'istruzione, il motore di query può nuovamente utilizzare l'indice per individuare la riga da modificare: questa è una buona notizia. E se si verifica una semplice modifica nei dati di una riga che non influisce sulle modifiche nelle colonne chiave, il processo di modifica sarà completamente indolore. Ma cosa succede se la modifica causa la divisione delle pagine contenenti i dati o il valore di una colonna chiave viene modificato causandone lo spostamento su un altro nodo dell'indice: ciò comporterà che l'indice potrebbe aver bisogno di una riorganizzazione che influisca su tutti gli indici e le operazioni associati , con conseguente calo diffuso della produttività.
Processi simili si verificano quando si chiama un operatore ELIMINARE. Un indice può aiutare a individuare i dati da eliminare, ma l'eliminazione dei dati stessi potrebbe comportare il rimescolamento della pagina. Per quanto riguarda l'operatore INSERIRE, il principale nemico di tutti gli indici: inizi ad aggiungere una grande quantità di dati, il che porta a cambiamenti negli indici e alla loro riorganizzazione e tutti ne soffrono.
Quindi considera i tipi di query sul tuo database quando pensi a quale tipo di indici e quanti crearne. Di più non significa meglio. Prima di aggiungere un nuovo indice a una tabella, considerare il costo non solo delle query sottostanti, ma anche la quantità di spazio su disco consumato, il costo di mantenimento delle funzionalità e degli indici, che può portare a un effetto domino su altre operazioni. La strategia di progettazione dell'indice è uno degli aspetti più importanti dell'implementazione e dovrebbe includere molte considerazioni, dalla dimensione dell'indice, al numero di valori univoci, al tipo di query supportate dall'indice.

È necessario creare un indice cluster su una colonna con una chiave primaria?

È possibile creare un indice cluster su qualsiasi colonna che soddisfi le condizioni richieste. È vero che un indice cluster e un vincolo di chiave primaria sono fatti l'uno per l'altro e sono una combinazione perfetta, quindi comprendi il fatto che quando crei una chiave primaria, verrà creato automaticamente un indice cluster se non ne è stato creato uno creato prima. Tuttavia, potresti decidere che un indice cluster avrebbe prestazioni migliori altrove e spesso la tua decisione sarà giustificata.
Lo scopo principale di un indice cluster è ordinare tutte le righe nella tabella in base alla colonna chiave specificata durante la definizione dell'indice. Ciò fornisce una ricerca rapida e un facile accesso ai dati della tabella.
La chiave primaria di una tabella può essere una buona scelta perché identifica in modo univoco ogni riga nelle tabelle senza dover aggiungere ulteriori dati. In alcuni casi, la scelta migliore sarà una chiave primaria surrogata, che non solo è unica, ma anche di piccole dimensioni e i cui valori aumentano in modo sequenziale, rendendo più efficienti gli indici non cluster basati su questo valore. Anche a Query Optimizer piace questa combinazione di un indice cluster e una chiave primaria perché l'unione delle tabelle è più veloce dell'unione in un altro modo che non utilizza una chiave primaria e il relativo indice cluster associato. Come ho detto, è una partita fatta in paradiso.
Infine, però, vale la pena notare che quando si crea un indice cluster ci sono diversi aspetti da considerare: quanti indici non cluster saranno basati su di esso, quanto spesso cambierà il valore della colonna dell'indice chiave e quanto è grande. Quando i valori nelle colonne di un indice cluster cambiano o l'indice non funziona come previsto, tutti gli altri indici nella tabella possono risentirne. Un indice cluster dovrebbe essere basato sulla colonna più persistente i cui valori aumentano in un ordine specifico ma non cambiano in modo casuale. L'indice deve supportare le query sui dati della tabella a cui si accede più frequentemente, in modo che le query traggano pieno vantaggio dal fatto che i dati sono ordinati e accessibili nei nodi radice, le foglie dell'indice. Se la chiave primaria si adatta a questo scenario, utilizzala. In caso contrario, scegli un diverso set di colonne.

Cosa succede se indicizzi una vista, è ancora una vista?

Una vista è una tabella virtuale che genera dati da una o più tabelle. Essenzialmente, si tratta di una query denominata che recupera i dati dalle tabelle sottostanti quando si esegue una query su quella vista. È possibile migliorare le prestazioni delle query creando un indice cluster e indici non cluster in questa vista, in modo simile a come si creano indici in una tabella, ma l'avvertenza principale è creare prima un indice cluster e quindi crearne uno non cluster.
Quando viene creata una vista indicizzata (vista materializzata), la definizione della vista stessa rimane un'entità separata. Dopotutto, questo è solo un operatore hardcoded SELEZIONARE, archiviati nel database. Ma l’indice è una storia completamente diversa. Quando crei un indice cluster o non cluster su un provider, i dati vengono salvati fisicamente su disco, proprio come un indice normale. Inoltre, quando i dati cambiano nelle tabelle sottostanti, l'indice della vista cambia automaticamente (questo significa che potresti voler evitare di indicizzare le viste su tabelle che cambiano frequentemente). In ogni caso, la vista rimane una vista: una vista delle tabelle, ma eseguita al momento, con gli indici corrispondenti.
Prima di poter creare un indice su una vista, è necessario che esso soddisfi diversi vincoli. Ad esempio, una vista può fare riferimento solo a tabelle di base, ma non ad altre viste e tali tabelle devono trovarsi nello stesso database. In realtà ci sono molte altre restrizioni, quindi assicurati di controllare la documentazione server SQL per tutti i dettagli sporchi.

Perché utilizzare un indice di copertura invece di un indice composito?

Innanzitutto, assicuriamoci di comprendere la differenza tra i due. Un indice composto è semplicemente un indice regolare che contiene più di una colonna. È possibile utilizzare più colonne chiave per garantire che ogni riga in una tabella sia univoca oppure potresti avere più colonne per garantire che la chiave primaria sia univoca oppure potresti provare a ottimizzare l'esecuzione di query richiamate di frequente su più colonne. In generale, tuttavia, maggiore è il numero di colonne chiave contenute in un indice, meno efficiente sarà l'indice, il che significa che gli indici compositi dovrebbero essere utilizzati con giudizio.
Come già detto, una query può trarre grandi vantaggi se tutti i dati richiesti si trovano immediatamente sulle foglie dell'indice, proprio come l'indice stesso. Questo non è un problema per un indice cluster perché tutti i dati sono già presenti (motivo per cui è così importante riflettere attentamente quando si crea un indice cluster). Ma un indice non cluster sulle foglie contiene solo colonne chiave. Per accedere a tutti gli altri dati, Query Optimizer richiede passaggi aggiuntivi, che possono aggiungere un sovraccarico significativo all'esecuzione delle query.
È qui che l'indice di copertura viene in soccorso. Quando definisci un indice non cluster, puoi specificare colonne aggiuntive per le colonne chiave. Ad esempio, supponiamo che l'applicazione esegua frequentemente query sui dati delle colonne ID ordine E Data dell'ordine sul tavolo Saldi:

SELECT IDOrdine, DataOrdine FROM Vendite WHERE IDOrdine = 12345 ;

È possibile creare un indice composto non cluster su entrambe le colonne, ma la colonna OrderDate aggiungerà solo un sovraccarico di manutenzione dell'indice senza fungere da colonna chiave particolarmente utile. La soluzione migliore sarebbe creare un indice di copertura sulla colonna chiave ID ordine e inoltre inclusa la colonna Data dell'ordine:

CREARE INDICE NON CLUSTERED ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);

Ciò evita gli svantaggi dell'indicizzazione delle colonne ridondanti pur mantenendo i vantaggi dell'archiviazione dei dati nelle foglie durante l'esecuzione delle query. La colonna inclusa non fa parte della chiave, ma i dati sono archiviati nel nodo foglia, l'indice foglia. Ciò può migliorare le prestazioni delle query senza alcun sovraccarico aggiuntivo. Inoltre, le colonne incluse nell'indice di copertura sono soggette a minori restrizioni rispetto alle colonne chiave dell'indice.

Il numero di duplicati in una colonna chiave è importante?

Quando crei un indice, devi provare a ridurre il numero di duplicati nelle colonne chiave. O più precisamente: cerca di mantenere il più basso possibile il tasso di ripetizione.
Se stai lavorando con un indice composito, la duplicazione si applica a tutte le colonne chiave nel loro insieme. Una singola colonna può contenere molti valori duplicati, ma la ripetizione tra tutte le colonne dell'indice dovrebbe essere minima. Ad esempio, crei un indice composto non cluster sulle colonne Nome di battesimo E Cognome, puoi avere molti valori John Doe e molti valori Doe, ma vuoi avere il minor numero possibile di valori John Doe o preferibilmente un solo valore John Doe.
Il rapporto di unicità dei valori di una colonna chiave è chiamato selettività dell'indice. Più valori univoci ci sono, maggiore è la selettività: un indice univoco ha la massima selettività possibile. Al motore delle query piacciono molto le colonne con valori di selettività elevati, soprattutto se tali colonne sono incluse nelle clausole WHERE delle query eseguite più frequentemente. Quanto più selettivo è l'indice, tanto più velocemente il motore di query può ridurre la dimensione del set di dati risultante. Lo svantaggio, ovviamente, è che le colonne con relativamente pochi valori univoci raramente saranno buoni candidati per l’indicizzazione.

È possibile creare un indice non cluster solo su un sottoinsieme specifico di dati di una colonna chiave?

Per impostazione predefinita, un indice non cluster contiene una riga per ogni riga della tabella. Naturalmente si può dire la stessa cosa di un indice cluster, presupponendo che tale indice sia una tabella. Ma quando si tratta di un indice non cluster, la relazione uno-a-uno è un concetto importante perché, a partire dalla versione SQLServer2008, hai la possibilità di creare un indice filtrabile che limiti le righe incluse al suo interno. Un indice filtrato può migliorare le prestazioni delle query perché... è di dimensioni più ridotte e contiene statistiche filtrate e più accurate di tutte quelle tabulari: questo porta alla creazione di piani di esecuzione migliorati. Un indice filtrato richiede inoltre meno spazio di archiviazione e minori costi di manutenzione. L'indice viene aggiornato solo quando cambiano i dati che corrispondono al filtro.
Inoltre, un indice filtrabile è facile da creare. Nell'operatore CREA INDICE devi solo indicarlo DOVE condizione del filtro. Ad esempio, puoi filtrare tutte le righe contenenti NULL dall'indice, come mostrato nel codice:

CREARE INDICE NON CLUSTERED ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) DOVE CarrierTrackingNumber NON È NULL ;

Possiamo, infatti, filtrare tutti i dati che non sono importanti nelle query critiche. Ma attenzione, perché... server SQL impone diverse restrizioni sugli indici filtrabili, come l'impossibilità di creare un indice filtrabile su una vista, quindi leggere attentamente la documentazione.
È anche possibile che tu possa ottenere risultati simili creando una vista indicizzata. Tuttavia, un indice filtrato presenta numerosi vantaggi, come la capacità di ridurre i costi di manutenzione e migliorare la qualità dei piani di esecuzione. Gli indici filtrati possono anche essere ricostruiti online. Prova questo con una vista indicizzata.

1) Concetto di indice
Indiceè uno strumento che fornisce un accesso rapido alle righe della tabella in base ai valori di una o più colonne.

C'è molta varietà in questo operatore perché non è standardizzato, poiché gli standard non affrontano i problemi di prestazione.

2) Creazione di indici
CREA INDICE
SU()

3) Modifica ed eliminazione degli indici
Per controllare l'attività dell'indice, viene utilizzato l'operatore:
ALTERARE L'INDICE
Per rimuovere un indice, utilizzare l'operatore:
INDICE DI CADUTA

a) Regole di selezione della tabella
1. Si consiglia di indicizzare le tabelle in cui è selezionato non più del 5% delle righe.
2. Le tabelle che non presentano duplicati nella clausola WHERE dell'istruzione SELECT devono essere indicizzate.
3. Non è pratico indicizzare le tabelle aggiornate frequentemente.
4. Non è appropriato indicizzare tabelle che occupano non più di 2 pagine (per Oracle sono meno di 300 righe), poiché la scansione completa non richiede più tempo.

b) Regole di selezione delle colonne
1. Chiavi primarie ed esterne: spesso utilizzate per unire tabelle, recuperare dati ed effettuare ricerche. Si tratta sempre di indici univoci con la massima utilità
2. Quando si utilizzano le opzioni di integrità referenziale, è sempre necessario un indice sull'FK.
3. Colonne in base alle quali i dati vengono spesso ordinati e/o raggruppati.
4. Colonne in cui viene eseguita frequentemente la ricerca nella clausola WHERE di un'istruzione SELECT.
5. Non dovresti creare indici su colonne descrittive lunghe.

c) Principi per la creazione di indici compositi
1. Gli indici compositi sono utili se le singole colonne hanno pochi valori univoci, ma un indice composito fornisce una maggiore unicità.
2. Se tutti i valori selezionati dall'istruzione SELECT appartengono a un indice composito, i valori vengono selezionati dall'indice.
3. È necessario creare un indice composto se la clausola WHERE utilizza due o più valori combinati con l'operatore AND.

d) Non è consigliabile creare
Si sconsiglia di creare indici su colonne, comprese quelle composite, che:
1. Utilizzato raramente per cercare, unire e ordinare i risultati delle query.
2. Contengono valori che cambiano frequentemente, il che richiede un aggiornamento frequente dell'indice, che rallenta le prestazioni del database.
3. Contenere un numero limitato di valori univoci (meno del 10% m/f) o un numero predominante di righe con uno o due valori (la città di residenza del fornitore è Mosca).
4. Nella clausola WHERE vengono applicate funzioni o un'espressione e l'indice non funziona.

e) Non dobbiamo dimenticare
Dovresti cercare di ridurre il numero di indici, poiché un numero elevato di indici riduce la velocità di aggiornamento dei dati. Pertanto, MS SQL Server consiglia di creare non più di 16 indici per tabella.
In genere, gli indici vengono creati a scopo di query e per mantenere l'integrità referenziale.
Se l'indice non viene utilizzato per le query, è necessario eliminarlo e garantire l'integrità referenziale utilizzando i trigger.

 

 
Questo è interessante: