CREATE INDEX (Transact-SQL)

La creazione di un indice cluster per una tabella (heap) e l'eliminazione e la ricreazione di un indice cluster esistente richiedono la disponibilità di un'area di lavoro aggiuntiva nel database per contenere l'ordinamento dei dati e una copia temporanea della tabella originale o dei dati dell'indice cluster esistenti. Per ulteriori informazioni, vedere Individuazione dei requisiti di spazio su disco per gli indici. Per ulteriori informazioni sugli indici cluster, vedere Creazione di indici cluster.

Quando esiste un indice univoco, ogni volta che vengono aggiunti nuovi dati tramite un'operazione di inserimento, Motore di database verifica l'eventuale presenza di valori duplicati. Le operazioni di inserimento che generano valori di chiave duplicati vengono sottoposte a rollback e in Motore di database viene visualizzato un messaggio di errore, anche nel caso in cui l'operazione di inserimento interessi più righe e crei un solo valore duplicato. Se si tenta di immettere dati per i quali è disponibile un indice univoco e la clausola IGNORE_DUP_KEY è impostata su ON, l'operazione ha esito negativo solo per le righe che violano l'indice UNIQUE. Per ulteriori informazioni sugli indici univoci, vedere Creazione di indici univoci.

Gli indici partizionati vengono creati e gestiti in modo analogo alle tabelle partizionate, ma, come gli indici normali, vengono trattati come oggetti di database separati. È possibile creare un indice partizionato per una tabella non partizionata, nonché creare un indice non partizionato per una tabella partizionata.

Se si crea un indice per una tabella partizionata senza specificare un filegroup in cui inserirlo, l'indice verrà partizionato in modo identico alla tabella sottostante, in quanto gli indici per impostazione predefinita vengono inseriti negli stessi filegroup delle tabelle sottostanti e, nel caso di una tabella partizionata, nello stesso schema di partizione con identiche colonne di partizionamento.

Quando si partiziona un indice cluster non univoco, per impostazione predefinita Motore di database aggiunge tutte le colonne di partizionamento all'elenco di chiavi di indice cluster, se non sono già presenti.

È possibile creare viste indicizzate per tabelle partizionate in modo analogo agli indici delle tabelle. Per ulteriori informazioni sugli indici partizionati, vedere Tabelle e indici partizionati.

La creazione di un indice cluster univoco per una vista consente un miglioramento delle prestazioni delle query, in quanto la vista viene archiviata nel database in modo analogo a una tabella con un indice cluster. Le viste indicizzate possono essere utilizzate da Query Optimizer per velocizzare l'esecuzione delle query. Non è necessario fare riferimento alla vista nella query affinché venga utilizzata da Query Optimizer per una sostituzione.

Per la creazione e la corretta implementazione di una vista indicizzata, è fondamentale eseguire le operazioni seguenti:

1. Verificare che le opzioni SET siano corrette per tutte le tabelle esistenti a cui verrà riferimento nella vista.

2. Verificare che le opzioni SET della sessione siano impostate in modo corretto prima di creare nuove tabelle e la vista.

3. Verificare che la definizione della vista sia deterministica.

4. Creare la vista con l'opzione WITH SCHEMABINDING.

5. Creare l'indice cluster univoco per la vista.

Opzioni SET necessarie per le viste indicizzate

La valutazione di una stessa espressione può produrre risultati diversi in Motore di database se sono attive diverse opzioni SET quando la query viene eseguita. Ad esempio, se l'opzione SET CONCAT_NULL_YIELDS_NULL è impostata su ON, l'espressione 'abc' + NULL restituisce il valore NULL. Se tuttavia l'opzione CONCAT_NULL_YIEDS_NULL è impostata su OFF, la stessa espressione restituisce 'abc'.

Per essere certi che le viste possano essere gestite in modo corretto e restituiscano risultati coerenti, è necessario utilizzare valori fissi per varie opzioni SET delle viste indicizzate. Le opzioni SET specificate nella tabella seguente devono essere impostate sui valori indicati nella colonna Valoreobbligatorio nelle seguenti circostanze:

• Quando viene creata la vista indicizzata.

• Quando viene eseguita un'operazione di inserimento, aggiornamento o eliminazione su una qualsiasi tabella utilizzata nella vista indicizzata, incluse operazioni quali la copia bulk, la replica e le query distribuite.

• Quando la vista indicizzata viene utilizzata in Query Optimizer per generare il piano di query.

  Opzioni SET	Valore obbligatorio	Valore server predefinito	Valore OLE DB e ODBC predefinito	Impostazione predefinita Valore DB-Library
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS*	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  *Quando il livello di compatibilità del database è impostato su 90 o su un valore superiore, l'impostazione di ANSI_WARNINGS su ON comporta l'impostazione implicita di ARITHABORT su ON. Se il livello di compatibilità del database è impostato su 80 o su un valore inferiore, l'opzione ARITHABORT deve essere impostata su ON in modo esplicito.

Se si utilizza una connessione server OLE DB o ODBC, l'unico valore da modificare è l'impostazione ARITHABORT. Tutti i valori DB-Library devono essere impostati in modo corretto a livello di server tramite sp_configure oppure dall'applicazione tramite il comando SET. Per ulteriori informazioni sulle opzioni SET, vedere Utilizzo delle opzioni di SQL Server.

Importante
È consigliabile impostare l'opzione utente ARITHABORT su ON per l'intero server immediatamente dopo la creazione della prima vista indicizzata o del primo indice in una colonna calcolata in qualsiasi database del server.

Funzioni deterministiche

Requisiti aggiuntivi

Un indice filtrato è un indice non cluster ottimizzato, adatto per le query tramite cui viene selezionata una piccola percentuale di righe da una tabella. Utilizza un predicato del filtro per indicizzare una parte dei dati di una tabella. Un indice filtrato progettato correttamente consente di migliorare le prestazioni di esecuzione delle query e di ridurre i costi di archiviazione e di manutenzione.

Opzioni SET necessarie per gli indici filtrati

Per informazioni sugli indici spaziali, vedere CREATE SPATIAL INDEX (Transact-SQL) e Utilizzo degli indici spaziali (Motore di database).

Per informazioni sugli indici XML, vedere CREATE XML INDEX (Transact-SQL) e Indici su colonne con tipo di dati XML.

La dimensione massima di una chiave di indice è 900 byte. È possibile creare indici su colonne varchar con una dimensione superiore a 900 byte se i dati esistenti nelle colonne non superano i 900 byte quando l'indice viene creato. Le successive operazioni di inserimento o aggiornamento nelle colonne che determinano l'aumento della dimensione totale oltre i 900 byte avranno tuttavia esito negativo. Per ulteriori informazioni, vedere Dimensione massima delle chiavi di indice. La chiave di indice di un indice cluster non può contenere colonne di tipo varchar con dati esistenti nell'unità di allocazione ROW_OVERFLOW_DATA. Se viene creato un indice cluster su una colonna di tipo varchar e i dati esistenti si trovano nell'unità di allocazione IN_ROW_DATA, le successive operazioni di inserimento o aggiornamento nella colonna che causerebbero uno spostamento dei dati all'esterno delle righe avranno esito negativo. Per ulteriori informazioni sulle unità di allocazione, vedere Organizzazione di tabelle e indici.

Gli indici non cluster possono includere colonne non chiave nel relativo livello foglia. Queste colonne non vengono considerate da Motore di database durante il calcolo della dimensione della chiave di indice. Per ulteriori informazioni, vedere Indice con colonne incluse.

Gli indici possono essere creati su colonne calcolate. Per le colonne calcolate è inoltre possibile impostare la proprietà PERSISTED. Questo significa che Motore di database archivia i valori calcolati nella tabella e li aggiorna quando vengono aggiornate altre colonne da cui dipende la colonna calcolata. Motore di database utilizza questi valori persistenti quando crea un indice sulla colonna e quando viene fatto riferimento all'indice all'interno di una query.

Per indicizzare una colonna calcolata, è necessario che tale colonna sia deterministica e precisa. La proprietà PERSISTED consente tuttavia di espandere i tipi di colonne calcolate indicizzabili, includendo i tipi seguenti:

• Colonne calcolate basate su funzioni Transact-SQL e CLR e metodi con tipo CLR definito dall'utente contrassegnati come deterministici dall'utente.

• Colonne calcolate basate su espressioni che sono deterministiche, secondo quanto definito in Motore di database, ma imprecise.

Per le colonne calcolate persistenti è necessario impostare le opzioni SET seguenti come illustrato nella sezione precedente "Opzioni SET necessarie per le viste indicizzate".

Il vincolo UNIQUE o PRIMARY KEY può includere una colonna calcolata a condizione che vengano soddisfatte tutte le condizioni per l'indicizzazione. In particolare, la colonna calcolata deve essere deterministica e precisa oppure deterministica e persistente. Per ulteriori informazioni sulle funzioni deterministiche, vedere Funzioni deterministiche e non deterministiche.

Le colonne calcolate derivate dai tipi di dati image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml possono essere indicizzate come colonna chiave o come colonna non chiave inclusa, a condizione che il tipo di dati della colonna calcolata sia supportato come colonna chiave o colonna non chiave dell'indice. Ad esempio, non è possibile creare un indice XML primario su una colonna xml calcolata. Se la dimensione della chiave di indice supera i 900 byte, viene visualizzato un messaggio di avviso.

La creazione di un indice su una colonna calcolata può impedire l'esecuzione di un'operazione di inserimento o di aggiornamento che in precedenza veniva eseguita correttamente. Questo problema si può verificare quando la colonna calcolata genera un errore aritmetico. Nella tabella seguente, ad esempio, nonostante la colonna calcolata c generi un errore aritmetico, l'istruzione INSERT viene eseguita correttamente.

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Se invece, dopo la creazione della tabella, viene creato un indice sulla colonna calcolata c, la stessa istruzione INSERT avrà esito negativo.

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Per ulteriori informazioni, vedere Creazione di indici per le colonne calcolate.

È possibile aggiungere colonne non chiave, o incluse, al livello foglia di un indice non cluster per migliorare le prestazioni di esecuzione delle query tramite la copertura della query. Questo significa che tutte le colonne a cui la query fa riferimento sono incluse nell'indice come colonne chiave o non chiave. In questo modo, per individuare tutte le informazioni necessarie, in Query Optimizer verrà eseguita un'analisi dell'indice, senza necessità di accedere ai dati della tabella o dell'indice cluster. Per ulteriori informazioni, vedere Indice con colonne incluse.

In SQL Server 2005 sono state introdotte nuove opzioni per gli indici ed è stata modificata la modalità di impostazione di tali opzioni. Per quanto riguarda la sintassi compatibile con le versioni precedenti, WITH option_name equivale a WITH ( <option_name> = ON ). Quando si impostano opzioni per gli indici, è necessario rispettare le regole seguenti:

• Per specificare nuove opzioni per gli indici è necessario utilizzare WITH (option_name= ON | OFF**)**.

• Non è possibile specificare opzioni utilizzando in una stessa istruzione sia la sintassi compatibile con le versioni precedenti, sia la nuova sintassi. Se, ad esempio, si specifica WITH (DROP_EXISTING, ONLINE = ON**)**, l'istruzione avrà esito negativo.

• Quando si crea un indice XML, le opzioni devono essere specificate con la sintassi WITH (option_name= ON | OFF**)**.

È possibile utilizzare la clausola DROP_EXISTING per ricompilare l'indice, aggiungere o eliminare colonne, modificare opzioni, modificare il tipo di ordinamento delle colonne oppure cambiare lo schema di partizione o il filegroup.

Se l'indice applica un vincolo PRIMARY KEY o UNIQUE e la definizione dell'indice non viene modificata in alcun modo, l'indice verrà eliminato e ricreato mantenendo il vincolo esistente. Se invece la definizione dell'indice viene modificata, l'istruzione avrà esito negativo. Per modificare la definizione di un vincolo PRIMARY KEY o UNIQUE, eliminare il vincolo e aggiungere un vincolo con la nuova definizione.

La clausola DROP_EXISTING consente un miglioramento delle prestazioni quando viene ricreato un indice cluster, con un set di chiavi identico oppure diverso, per una tabella che include anche indici non cluster. La clausola DROP_EXISTING sostituisce l'esecuzione di un'istruzione DROP INDEX sull'indice cluster precedente e quindi di un'istruzione CREATE INDEX per il nuovo indice cluster. Gli indici non cluster vengono ricompilati una sola volta e poi solo in caso di modifica della relativa definizione. La clausola DROP_EXISTING non ricompila gli indici non cluster quando la definizione dell'indice contiene gli stessi valori dell'indice originale relativi al nome di indice, alle colonne chiave e di partizione, all'attributo di univocità e al tipo di ordinamento.

Indipendentemente dal fatto che gli indici non cluster vengano ricompilati o meno, rimangono sempre nei filegroup o negli schemi di partizione originali e utilizzano le funzioni di partizione originali. Se un indice cluster viene ricompilato in un filegroup o in uno schema di partizione diverso, gli indici non cluster non vengono spostati in funzione della nuova posizione dell'indice cluster. Pertanto, anche gli indici non cluster in precedenza allineati con l'indice cluster potrebbero non essere più allineati. Per ulteriori informazioni sull'allineamento di indici partizionati, vedere Linee guida specifiche per gli indici partizionati.

La clausola DROP_EXISTING non ripete l'ordinamento dei dati se vengono utilizzate le stesse colonne chiave indice nello stesso ordine e con lo stesso tipo di ordinamento crescente o decrescente, a meno che nell'istruzione dell'indice non venga specificato un indice non cluster e l'opzione ONLINE sia impostata su OFF. Se l'indice cluster è disabilitato, l'operazione CREATE INDEX WITH DROP_EXISTING deve essere eseguita con l'opzione ONLINE impostata su OFF. Se un indice non cluster è disabilitato e non è associato a un indice cluster disabilitato, l'operazione CREATE INDEX WITH DROP_EXISTING può essere eseguita con l'opzione ONLINE impostata su OFF o ON.

Quando vengono eliminati o ricompilati indici con un numero di extent pari o superiore a 128, Motore di database posticipa le effettive deallocazioni delle pagine e i blocchi associati fino al termine del commit della transazione. Per ulteriori informazioni, vedere Eliminazione e ricostruzione di oggetti di grandi dimensioni.

Per l'esecuzione di operazioni sugli indici online, è necessario attenersi alle indicazioni seguenti:

• Non è possibile modificare, troncare o eliminare la tabella sottostante mentre è in corso un'operazione sull'indice online.

• Durante l'operazione sull'indice lo spazio su disco necessario aumenta temporaneamente. Per ulteriori informazioni, vedere Individuazione dei requisiti di spazio su disco per gli indici.

• È possibile eseguire operazioni online su indici partizionati e indici che contengono colonne calcolate persistenti o colonne incluse.

Per ulteriori informazioni, vedere Esecuzione di operazioni online su indici.

Se ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, sono consentiti blocchi a livello di riga, pagina e tabella per l'accesso all'indice. Motore di database sceglie il tipo di blocco più appropriato e può eseguire l'escalation del blocco dal livello di riga o pagina al livello di tabella. Per ulteriori informazioni, vedere Escalation blocchi (Motore di database).

Se ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, sono consentiti solo blocchi a livello di tabella per l'accesso all'indice.

Per ulteriori informazioni sulla configurazione della granularità dei blocchi per un indice, vedere Personalizzazione dei blocchi per un indice.

Per ottenere informazioni sugli indici, è possibile utilizzare viste del catalogo, funzioni di sistema e stored procedure di sistema. Per ulteriori informazioni, vedere Visualizzazione delle informazioni relative agli indici.

La compressione dei dati è descritta nell'argomento Creazione di tabelle e di indici compressi. Di seguito sono illustrati i punti principali da considerare:

• La compressione può consentire di archiviare più righe in una pagina, ma non di modificare la dimensione massima della riga.

• Alle pagine non foglia di un indice non può essere applicata la compressione a livello di pagina, ma può essere applicata la compressione a livello di riga.

• Ogni indice non cluster dispone di un'impostazione di compressione separata e non eredita l'impostazione di compressione della tabella sottostante.

• Quando un indice cluster viene creato in un heap, tale indice eredita lo stato di compressione dell'heap, a meno che non venga specificato uno stato di compressione alternativo.

Agli indici partizionati vengono applicate le restrizioni seguenti:

• Non è possibile modificare l'impostazione di compressione di una singola partizione se la tabella include indici non allineati.

• La sintassi ALTER INDEX <index> ... REBUILD PARTITION... consente di ricompilare la partizione specificata dell'indice.

• La sintassi ALTER INDEX <index> ... REBUILD WITH ... consente di ricompilare tutte le partizioni dell'indice.

Per valutare il modo in cui la modifica dello stato di compressione influirà su una tabella, un indice o una partizione, utilizzare la stored procedure sp_estimate_data_compression_savings.

Nell'esempio seguente viene creato un indice non cluster sulla colonna BusinessEntityID della tabella Purchasing.ProductVendor.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (BusinessEntityID); 
GO

Nell'esempio seguente viene creato un indice composto non cluster sulle colonne SalesQuota e SalesYTD della tabella Sales.SalesPerson.

USE AdventureWorks2008R2
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

Nell'esempio seguente viene creato un indice non cluster univoco sulla colonna Name della tabella Production.UnitMeasure. Questo indice impone l'univocità dei dati inseriti nella colonna Name.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

Nella query seguente viene verificato il vincolo di univocità mediante un tentativo di inserimento di una riga con lo stesso valore di una riga esistente.

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
    VALUES ('OC', 'Ounces', GetDate());

Il messaggio di errore risultante è:

Server: Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

Nell'esempio seguente viene illustrato l'effetto dell'opzione IGNORE_DUP_KEY tramite l'inserimento di più righe in una tabella temporanea prima con questa opzione impostata su ON e quindi con questa opzione impostata su OFF. Nella tabella #Test viene inserita una singola riga che genererà intenzionalmente un valore duplicato quando verrà eseguita la seconda istruzione INSERT su più righe. Il calcolo delle righe della tabella restituisce il numero di righe inserite.

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Di seguito sono riportati i risultati della seconda istruzione INSERT.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows

--------------

38

Si noti che le righe della tabella Production.UnitMeasure che non violano il vincolo di univocità sono state inserite correttamente. Nonostante sia stato visualizzato un messaggio di avviso e sia stata ignorata la riga duplicata, non è stato eseguito un rollback dell'intera transazione.

A questo punto vengono eseguite di nuovo le stesse istruzioni, ma l'opzione IGNORE_DUP_KEY è impostata su OFF.

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Di seguito sono riportati i risultati della seconda istruzione INSERT.

Server: Msg 2601, Level 14, State 1, Line 5

Cannot insert duplicate key row in object '#Test' with unique index

'AK_Index'. The statement has been terminated.

Number of rows

--------------

1

Si noti che nella tabella non è stata inserita alcuna riga della tabella Production.UnitMeasure, sebbene la violazione del vincolo dell'indice UNIQUE fosse determinata da una sola riga.

Nell'esempio seguente viene eliminato e ricreato un indice esistente sulla colonna ProductID della tabella Production.WorkOrder tramite l'opzione DROP_EXISTING. Vengono inoltre impostate le opzioni FILLFACTOR e PAD_INDEX.

USE AdventureWorks2008R2;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

Nell'esempio seguente vengono creati una vista e un indice per tale vista, quindi vengono eseguite due query che utilizzano la vista indicizzata.

USE AdventureWorks2008R2;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

Nell'esempio seguente viene creato un indice non cluster con una colonna chiave (PostalCode) e quattro colonne non chiave (AddressLine1, AddressLine2, City, StateProvinceID), quindi viene eseguita una query che utilizza tale indice. Per visualizzare l'indice selezionato da Query Optimizer, nel menu Query di SQL Server Management Studio selezionare Includi piano di esecuzione effettivo prima di eseguire la query.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
    ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

Nell'esempio seguente viene creato un indice partizionato non cluster nello schema di partizione esistente TransactionsPS1. In questo esempio si presuppone che sia stato installato l'esempio di indice partizionato.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

Nell'esempio seguente viene creato un indice filtrato nella tabella Production.BillOfMaterials. Il predicato del filtro può includere colonne che non sono colonne chiave nell'indice filtrato. Il predicato in questo esempio consente di selezionare solo le righe in cui EndDate non ha un valore NULL.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

Nell'esempio seguente viene creato un indice in una tabella non partizionata utilizzando la compressione di riga.

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

Nell'esempio seguente viene creato un indice in una tabella partizionata utilizzando la compressione di riga in tutte le partizioni dell'indice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

Nell'esempio seguente viene creato un indice in una tabella partizionata utilizzando la compressione di pagina nella partizione 1 dell'indice e la compressione di riga nelle partizioni da 2 a 4 dell'indice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO