SQL Server & Delphi

Quante volte avete risposto con un No alla domanda: "E’ possibile creare due o più indici clustered sulla stessa tabella ?"

Non è infatti possibile avere due indici clustered sulla stessa tabella, facciamo però alcune considerazioni...

Un indice clustered ordina e memorizza i dati di una tabella in funzione della chiave dell’indice stesso. L’indice clustered è implementato con una struttura ad albero di tipo B-Tree, ogni pagina (di memoria) facente parte di questa struttura è chiamata nodo dell’indice. Il nodo di partenza, al livello più alto (top node), è chiamato nodo di root, mentre i nodi ai livelli più bassi (bottom node) sono chiamati nodi foglia. Gli altri livelli, contenuti tra il nodo di root e i nodi foglia sono conosciuti come nodi intermedi.

Ogni pagina nei livelli intermedi o nei livelli più bassi contiene un puntatore sia alla pagina precedente che a quella successiva, formando così una lista doppiamente collegata. Questa struttura, propriamente detta B-Tree, fornisce un meccanismo molto efficiente per velocizzare il processo di localizzazione dei dati.

L’indice clustered determina quindi l’ordinamento con cui vengono memorizzate le righe di una tabella, e come possiamo facilmente immaginare, per ogni tabella potrà esistere solo un indice clustered.

Ipotizziamo ora di creare la tabella HumanResources.Employee_L sul database AdventureWorks con il seguente T-SQL:

USE AdventureWorks

-- Creazione tabella HumanResources.Employee_L con l'indice
-- clustered creato by default sulla colonna EmployeeID
CREATE TABLE HumanResources.Employee_L
(EmployeeID INT IDENTITY(1, 1) NOT NULL,
 ContactID INT NOT NULL,
 NationalIDNumber NVARCHAR(15) NOT NULL,
 LoginID NVARCHAR(256) NOT NULL,
 HireDate DATETIME NOT NULL
 PRIMARY KEY (EmployeeID))

ALTER TABLE HumanResources.Employee_L ADD CONSTRAINT FK_Person_ContactID
FOREIGN KEY (ContactID) REFERENCES Person.Contact (ContactID)

Quando viene creata la PRIMARY KEY su una tabella, SQL Server implementa automaticamente (dietro le quinte) anche un indice UNIQUE. By default l’indice UNIQUE è di tipo clustered (ovviamente se non ne esiste già uno).

Durante la creazione della tabella HumanResources.Employee_L, l’engine di SQL Server ha creato automaticamente anche l’indice clustered sulla colonna EmployeeID.

Vogliamo creare ora la vista HumanResources.VW_Employee_LContact che permette di estrarre dati dalle tabelle HumanResources.Employee_L e Person.Contact:

-- Creazione della vista HumanResources.VW_Employee_LContact
CREATE VIEW HumanResources.VW_Employee_LContact
WITH SCHEMABINDING AS
  SELECT
    e.EmployeeID,
    e.HireDate,
    c.Title,
    c.FirstName,
    c.LastName,
    c.Phone
  FROM HumanResources.Employee_L e
  JOIN Person.Contact c ON c.ContactID = e.ContactID

Per assicurarci ottime performance nell’utilizzo della vista HumanResources.VW_Employee_LContact decidiamo di sfruttare una nuova funzionalità di SQL Server 2005: la possibilità di creare viste indicizzate. Una vista indicizzata è una vista che ha un indice univoco di tipo clustered che insiste su una o più colonne della vista. In una vista indicizzata il result set (dati) è memorizzato nelle pagine di livello più basso (foglie) dell’indice clustered. SQL Server può accedere molto velocemente ai dati della vista passando attraverso l’indice clustered.

Utilizzando il comando CREATE INDEX per creare l’indice IX_VW_Employee_Lcontact sulla vista HumanResources.VW_Employee_LContact:

-- Creazione indice clustered sulla vista VW_Employee_LContact
CREATE UNIQUE CLUSTERED INDEX IX_VW_Employee_LContact
ON HumanResources.VW_Employee_LContact
(
  [HireDate] ASC
)

L’indice creato sulla vista è di tipo unique clustered, siamo quindi in presenza di una vista materializzata sui dati delle tabelle HumanResources.Employee_L e Person.Contact, in particolare l’implementazione dell’indice IX_VW_Employee_LContact ha dato luogo alla creazione di una struttura ad albero di tipo B-Tree in cui i dati sono ordinati in funzione del campo HireDate della tabella HumanResources.Employee_L.

Se una query, che sta per essere eseguita, contiene riferimenti alle colonne della tabella di base HumanResources.Employee_L che sono presenti anche nella vista indicizzata e il query optimizer stima che l'uso della vista indicizzata fornisce il più basso costo di accesso, il query optimizer sceglie di percorrere l'indice clustered creato sulla vista, nello stesso modo in cui sceglie gli indici della tabella di base.

Query optimizer potrà quindi utilizzare, qualora lo ritenga più performante, anche l’indice IX_VW_Employee_LContact per accedere ai dati della tabella HumanResources.Employee_L.

Ragionando in astratto, possiamo dire di aver creato due indici clustered sui dati della tabella HumanResources.Employee_L.