Indici sulle colonne calcolate

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

• Requisiti di proprietà

• Requisiti determinismo

• Requisiti di precisione

• Requisiti del tipo di dati

• Requisiti delle opzioni SET

Requisiti di proprietà

Tutti i riferimenti di funzione nella colonna calcolata devono avere lo stesso proprietario della tabella.

Requisiti deterministico

Importante

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

Il computed_column_expression deve essere deterministico. Un computed_column_expression è deterministico quando si verifica una o più delle condizioni seguenti:

• Tutte le funzioni a cui fa riferimento l'espressione sono deterministiche e precise. Queste funzioni includono funzioni definite dall'utente e predefinite. Per altre informazioni, vedere Funzioni deterministiche e non deterministiche. Le funzioni potrebbero non essere imprecise se la colonna calcolata è PERSISTED. Per altre informazioni, vedere Creazione di indici in colonne calcolate persistenti più avanti in questo argomento.

• Tutte le colonne a cui viene fatto riferimento nell'espressione provengono dalla tabella contenente la colonna calcolata.

• Nessun riferimento a colonne è utilizzato per estrarre dati da più righe. Ad esempio, le funzioni di aggregazione come SUM o AVG dipendono dai dati di più righe e renderebbero un computed_column_expression non deterministico.

• 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 che la colonna possa essere indicizzata. Le espressioni di tipo CLR definite dall'utente sono consentite nelle definizioni delle colonne calcolate. Le colonne calcolate il cui tipo è un tipo CLR definito dall'utente possono essere indicizzate purché il tipo sia paragonabile. Per altre informazioni, vedere Tipi CLR definiti dall'utente.

Annotazioni

Quando si fa riferimento a valori letterali stringa del tipo di dati date nelle colonne calcolate indicizzate in SQL Server, è consigliabile convertire in modo esplicito il valore letterale nel tipo di data desiderato usando uno stile di formato data deterministico. Per un elenco degli stili del formato di data deterministici, vedere CAST e CONVERT. Le espressioni che comportano la conversione implicita di stringhe di caratteri in tipi di dati date vengono considerate non deterministiche, a meno che il livello di compatibilità del database non sia impostato su 80 o versioni precedenti. 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 perché la stringa '30 listopad 1996' indica mesi diversi in lingue diverse. Analogamente, nell'espressione DATEADD(mm,3,'2000-12-01'), il motore di database interpreta la stringa '2000-12-01' in base all'impostazione DATEFORMAT.

Anche la conversione implicita dei dati di caratteri non Unicode tra regole di confronto viene considerata non deterministica, a meno che il livello di compatibilità non sia impostato su 80 o versioni precedenti.

Quando l'impostazione del livello di compatibilità del database è 90, non è possibile creare indici in colonne calcolate che contengono queste espressioni. Tuttavia, le colonne calcolate esistenti che contengono queste espressioni in un database aggiornato sono manutenibili. Se si usano colonne calcolate indicizzate che contengono conversioni implicite da stringa a data, per evitare possibili danneggiamenti dell'indice, assicurarsi che le impostazioni LANGUAGE e DATEFORMAT siano coerenti nei database e nelle applicazioni.

Requisiti di precisione

Il computed_column_expression deve essere preciso. Un computed_column_expression è preciso quando una o più delle condizioni seguenti sono vere:

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

• Non utilizza un tipo di dati float o real nella sua definizione. Nell'istruzione seguente, ad esempio, la colonna y è int e 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);  
  

Annotazioni

Qualsiasi float espressione o real è considerata imprecisa e non può essere una chiave di un indice. Un'espressione float o real può essere usata in una vista indicizzata ma non come chiave. Questo vale anche per le colonne calcolate. Qualsiasi funzione, espressione o funzione definita dall'utente viene considerata imprecisa se contiene espressioni float o real . Sono inclusi quelli logici (confronti).

La proprietà IsPrecise della funzione COLUMNPROPERTY indica se un computed_column_expression è preciso.

Requisiti del tipo di dati

• Il computed_column_expression definito per la colonna calcolata non può valutare ai tipi di dati text, ntext o image.

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

• Le colonne calcolate derivate dai tipi di dati image, ntext e text possono essere colonne non chiave (incluse) in un indice non clusterizzato, a condizione che il tipo di dati della colonna calcolata sia consentito come colonna di indice non chiave.

Requisiti delle opzioni SET

• L'opzione ANSI_NULLS livello di connessione deve essere impostata su ON quando viene eseguita l'istruzione CREATE TABLE o ALTER TABLE che definisce la colonna calcolata. La funzione OBJECTPROPERTY indica se l'opzione è attiva tramite la proprietà IsAnsiNullsOn .

• La connessione in cui viene creato l'indice e tutte le connessioni che provano le istruzioni INSERT, UPDATE o DELETE che modificheranno i valori nell'indice devono avere sei opzioni SET impostate su ON e un'opzione impostata su OFF. Optimizer ignora un indice in una colonna calcolata per qualsiasi istruzione SELECT eseguita da una connessione che non dispone di queste stesse impostazioni di opzione.

  • L'opzione NUMERIC_ROUNDABORT deve essere impostata su OFF e le opzioni seguenti devono essere impostate su ON:

  • ANSI_NULLS

  • ANSI_PADDING (impostazione di riempimento ANSI)

  • ANSI_WARNINGS

  • ARITHABORT

  • CONCAT_NULL_YIELDS_NULL (Quando concatenato a NULL, il risultato è NULL)

  • IDENTIFICATORE_QUOTATO

  Quando il livello di compatibilità del database è impostato su 90 o su un valore maggiore, l'impostazione di ANSI_WARNINGS su ON comporta anche l'impostazione implicita di ARITHABORT su ON.

Creazione di indici in colonne calcolate persistenti

È possibile creare un indice in una colonna calcolata definita con un'espressione deterministica, ma imprecisa, se la colonna è contrassegnata come PERSISTED nell'istruzione CREATE TABLE o ALTER TABLE. Ciò significa che il motore di database usa questi valori persistenti quando crea un indice nella colonna e quando viene fatto riferimento all'indice in una query. Questa opzione consente di creare un indice in una colonna calcolata quando .NET Framework è deterministico e preciso.

Contenuto correlato

COLUMNPROPERTY (Transact-SQL)