Indici TSQL

Gli indici sono speciali tabelle di ricerca utilizzate dal motore d SQL per un accesso rapido ai dati.

Un indice non è altro che un puntatore ad una tabella.

Un indice aiuta a velocizzare le query SELECT e le clausole WHERE, ma rallenta l'immissione dei dati, con le istruzioni UPDATE e INSERT; occupano inoltre spazio su disco per cui è quindi molto importante dosare bene la quantità di indici creati in una tabella

Quando si effettua una ricerca su una tabella tramite un indice, SQL invece di scorrere l'intera tabella per trovare i risultati, guarda nell'indice, e successivamente estrae i record a cui l'indice sta punando

Create INDEX

Vediamo la sintassi per la creazione dei un indice in una tabella

CREATE INDEX index_name on table_name (column1, column2)

Drop INDEX

E' utilizzata per eliminare un indice da una tabella

DROP INDEX index_name on table_name

INCLUDE

In SQL è possibile creare degli indici detti di copertura, tramite la parola chiave INCLUDE. il funzionamento è semplice. Se un indice contiene come colonne di copertura tutte quelle che servono alla nostra query, non sarà necessario risalire al puntamento nella tabella originale, ma basterà restituire i dati direttamente dall'indice. Questo ovviamente fa aumentare le prestazioni in lettura.
Il rovesio della medagli è che le operazioni di scrittura saranno un po' più lente e lo spazio occupato su disco aumenterà

CREATE INDEX index_name on table_name (column1, column2)  INCLUDE (column3, column4)

Esempio

CREATE TABLE #ordini
(
	Societa INT,
	numeroOrdine int,
	importoOrdine MONEY
)

/*inserisco un po' di dati*/
DECLARE 
	@i int,
	@j int

SET @i = 1
SET @j = 1
WHILE @j < 1000	
BEGIN
	INSERT INTO #ordini (Societa, numeroOrdine, importoOrdine)
	VALUES (@i, @j, 100 * RAND())
	SET @j = @j + 1
END

SET @i = 2
SET @j = 1
WHILE @j < 50	
BEGIN
	INSERT INTO #ordini (Societa, numeroOrdine, importoOrdine)
	VALUES (@i, @j, 100 * RAND())
	SET @j = @j + 1
END

SET @i = 3
SET @j = 1
WHILE @j < 10000	
BEGIN
	INSERT INTO #ordini (Societa, numeroOrdine, importoOrdine)
	VALUES (@i, @j, 100 * RAND())
	SET @j = @j + 1
END

SET @i = 4
SET @j = 1
WHILE @j < 3	
BEGIN
	INSERT INTO #ordini (Societa, numeroOrdine, importoOrdine)
	VALUES (@i, @j, 100 * RAND())
	SET @j = @j + 1
END

Effettuiamo la select senza inserire indici

/*senza indici*/
SELECT 
	SUM(importoOrdine) 
FROM #ordini 
WHERE 
	Societa = 1

Se guardiamo il piano di esecuzione della query, possiamo notare che abbiamo un "TABLE Scan" sulla tabella Ordini

/*con indice semplice*/
CREATE INDEX ix_ordini ON #ordini (societa)
GO
SELECT 
	SUM(importoOrdine) 
FROM #ordini 
WHERE 
	Societa = 1

Continuiamo ad avere un "TABLE Scan" sulla tabella Ordini, in quanto l'indice non è sufficientemente restrittivo

/*con indice semplice e ricerca diversa*/
CREATE INDEX ix_ordini ON #ordini (societa)
GO
SELECT 
	SUM(importoOrdine) 
FROM #ordini 
WHERE 
	Societa = 2

In questo caso poichè i dati della società 4 sono pochi, SQL riesce a fare un Index Seek, ma poi deve fare un RID Lookup (60% del peso della query) per recuperare l'importo dell'ordine dalla tabella Eliminiamo l'indice e creiamone un'altro con dei campi di copertura.
Abbiamo imparato che le prestazioni ed il comportamento degli indici dipendono fortemente dalla cardinalità delle tabelle.

/*Eliminiamo il vecchio indice*/
DROP INDEX ix_ordini ON #ordini

/*con indice di copertura*/
CREATE INDEX ix_ordini2 ON #ordini (societa) INCLUDE (importoOrdine)
GO
SELECT 
	SUM(importoOrdine) 
FROM #ordini 
WHERE 
	Societa = 1

Così facendo otteniamo un Index Seek sull'indice creato, poichè non è più necessario risalire al puntamento sulla tabella di origine

Possiamo infine eliminare la tabella temporanea creata

DROP TABLE #ordini