Creazione di indici per le colonne calcolate

È possibile definire gli indici per le colonne calcolate purché siano soddisfatti i requisiti seguenti:

• Requisiti di proprietà

• Requisiti di determinismo

• Requisiti di precisione

• Requisiti del tipo di dati

• Requisiti dell'opzione SET

Requisiti di proprietà

Tutti i riferimenti a funzioni nella colonna calcolata devono avere lo stesso proprietario della tabella.

Requisiti di determinismo

Le espressioni sono deterministiche se restituiscono sempre lo stesso risultato per un determinato set di input. La proprietà IsDeterministic della funzione COLUMNPROPERTY indica se computed_column_expression è deterministica.

• È necessario che computed_column_expression sia deterministica. computed_column_expression è deterministica quando viene soddisfatta una o più delle condizioni seguenti:

  • Tutte le funzioni alle quali l'espressione fa riferimento sono deterministiche e precise. Queste funzioni includono funzioni definite dall'utente e funzioni predefinite. Per ulteriori informazioni, vedere Funzioni deterministiche e non deterministiche. Le funzioni potrebbero non essere precise se la colonna calcolata è PERSISTED. Per ulteriori informazioni, vedere Creazione di indici per colonne calcolate persistenti di seguito in questo argomento.

  • Tutte le colonne alle quali viene fatto riferimento nell'espressione appartengono alla tabella contenente la colonna calcolata.

  • Nessun riferimento a una colonna estrae dati da più righe. Ad esempio, funzioni di aggregazione quali SUM o AVG dipendono dai dati presenti in più righe e renderebbero non deterministica l'espressione computed_column_expression.

  • È priva di accesso ai dati di sistema o ai dati utente.

• Qualsiasi colonna calcolata che contiene un'espressione CLR (Common Language Runtime) deve essere deterministica e contrassegnata come PERSISTED prima di poter essere indicizzata. Le espressioni CLR definite dall'utente sono consentite nelle definizioni delle colonne calcolate. Le colonne calcolate di tipo CLR definito dall'utente possono essere indicizzate purché il tipo sia confrontabile. Per ulteriori informazioni, vedere Tipi CLR definiti dall'utente.

Nota

Quando si fa riferimento ai valori letterali stringa del tipo di dati relativo alla data in SQL Server, si consiglia di convertire in modo esplicito il valore letterale nel tipo di data che si desidera utilizzando uno stile di formato di data deterministico.Per un elenco degli stili del formato di data deterministici, vedere CAST e CONVERT. Le espressioni che prevedono la conversione implicita delle stringhe di carattere nei tipi di dati relativi alla data vengono considerate non deterministiche, a meno che il livello di compatibilità del database non venga impostato su 80 o su un valore inferiore.Ciò è dovuto al fatto che i risultati dipendono dalle impostazioni LANGUAGE e DATEFORMAT della sessione del server. Ad esempio, i risultati dell'espressione CONVERT (datetime, '30 listopad 1996', 113) dipendono dall'impostazione LANGUAGE in quanto la stringa '30 listopad 1996' indica mesi diversi in lingue diverse. Analogamente, nell'espressione DATEADD(mm,3,'2000-12-01'), Motore di database interpreta la stringa '2000-12-01' in base all'impostazione DATEFORMAT.

Anche la conversione implicita dei dati di tipo carattere non Unicode tra regole di confronto viene considerata non deterministica, a meno che il livello di compatibilità non sia impostato su un valore minore o uguale a 80.

Se l'impostazione del livello di compatibilità del database è 90, non è possibile creare indici su colonne calcolate contenenti tali espressioni. Tuttavia, le colonne calcolate esistenti che includono queste espressioni da un database aggiornato sono gestibili. Se si utilizzano colonne calcolate che includono conversioni implicite da valori di tipo stringa a valori di tipo data, verificare che le impostazioni LANGUAGE e DATEFORMAT siano consistenti nei database e nelle applicazioni per evitare l'eventuale danneggiamento dell'indice.

Requisiti di precisione

È necessario che computed_column_expression sia precisa. computed_column_expression è precisa quando viene soddisfatta una o più delle condizioni seguenti:

• Non è un'espressione dei tipi di dati float o real.

• Nella definizione dell'espressione non viene utilizzato il tipo di dati float o real. Ad esempio, nell'istruzione seguente la colonna y è di tipo int ed è deterministica, ma non precisa:

  CREATE TABLE t2 (a int, b int, c int, x float, 
     y AS CASE x 
           WHEN 0 THEN a 
           WHEN 1 THEN b 
           ELSE c 
        END)
  

Nota

Le espressioni di tipo float o real sono considerate non precise e non possono essere utilizzate come chiavi di un indice. Le espressioni float o real sono quindi utilizzabili in una vista indicizzata ma non come chiavi. Questa considerazione è valida anche per le colonne calcolate. Funzioni, espressioni oppure funzioni definite dall'utente sono considerate non precise se includono espressioni float oppure real. Sono comprese le espressioni logiche (confronti).

La proprietà IsPrecise della funzione COLUMNPROPERTY indica se un'espressione computed_column_expression è precisa.

Requisiti del tipo di dati

• L'espressione computed_column_expression definita per la colonna calcolata non può restituire i tipi di dati text, ntext oppure image.

• Le colonne calcolate derivate dai tipi di dati image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml possono essere indicizzate purché il tipo di dati della colonna calcolata sia consentito come colonna chiave indice.

• Le colonne calcolate derivate dai tipi di dati image, ntext e text possono essere colonne non chiave (incluse) in un indice non cluster purché il tipo di dati della colonna calcolata sia consentito come colonna non chiave dell'indice.

Requisiti dell'opzione SET

• Quando viene eseguita l'istruzione CREATE TABLE o ALTER TABLE che definisce la colonna calcolata, è necessario impostare su ON l'opzione a livello di connessione ANSI_NULL. La proprietà IsAnsiNullsOn della funzione OBJECTPROPERTY indica se l'opzione è impostata su ON.

• Per la connessione in corrispondenza della quale viene creato l'indice e per tutte le connessioni che tentano di eseguire istruzioni INSERT, UPDATE o DELETE che modificano i valori dell'indice, sei opzioni SET devono essere impostate su ON e un'opzione SET deve essere impostata su OFF. Per tutte le eventuali istruzioni SELECT eseguite da una connessione per la quale non sono state definite esattamente le impostazioni delle opzioni indicate di seguito, Query Optimizer ignora gli indici definiti su una colonna calcolata.

  • L'opzione NUMERIC_ROUNDABORT deve essere impostata su OFF e le opzioni seguenti su ON.

  • ANSI_NULLS

  • ANSI_PADDING

  • ANSI_WARNINGS

  • ARITHABORT

  • CONCAT_NULL_YIELDS_NULL

  • QUOTED_IDENTIFIER

  Quando il livello di compatibilità del database viene impostato su 90, 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 esplicitamente su ON. Per ulteriori informazioni, vedere Opzioni SET che hanno effetto sui risultati.

Creazione di indici per colonne calcolate persistenti

È possibile creare un indice su una colonna calcolata. definita da un'espressione deterministica, ma non precisa, se la colonna è contrassegnata come PERSISTED nell'istruzione CREATE TABLE oppure ALTER TABLE. Questo significa che Motore di database archivia i valori calcolati nella tabella e li aggiorna quando qualsiasi altra colonna da cui dipende la colonna calcolata viene aggiornata. Motore di database utilizza questi valori persistenti quando crea un indice nella colonna, e quando all'indice viene fatto riferimento in una query. Questa opzione consente di creare un indice in una colonna calcolata quando Motore di database non è in grado di verificare esattamente se una funzione che restituisce espressioni di colonne calcolate, in particolare una funzione CLR creata in .NET Framework, sia deterministica e precisa.

Vedere anche

Riferimento

sp_dbcmptlevel (Transact-SQL)

COLUMNPROPERTY (Transact-SQL)

Concetti

Creazione di indici (Motore di database)