Condividi tramite

  • Articolo

Utilizzo di dati sql_variant

Il tipo di dati sql_variant funziona in modo analogo al tipo di dati variant in MicrosoftVisual Basic. Il tipo sql_variant consente l'archiviazione di valori appartenenti a tipi di dati diversi in un singolo parametro, colonna o variabile. In una colonna sql_variant possono ad esempio essere presenti valori int, decimal, char, binary e nchar. In ogni istanza di una colonna sql_variant vengono registrati il valore dei dati e le informazioni sui metadati, tra cui tipo di dati di base, dimensioni massime, scala, precisione e regole di confronto.

Regole per l'utilizzo del tipo sql_variant

Le regole seguenti riguardano l'utilizzo del tipo di dati sql_variant.

Assegnazione del valore generale

  • Gli oggetti sql_variant possono includere qualsiasi tipo di dati di SQL Server, ad eccezione dei tipi text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp e Microsoft.NET Framework CLR (Common Language Runtime) definiti dall'utente. Per un'istanza di dati sql_variant, inoltre, il tipo di dati di base sottostante non può corrispondere a sql_variant.

  • È possibile specificare costanti di qualsiasi tipo in predicati o assegnazioni che fanno riferimento a colonne sql_variant.

  • Se un valore sql_variant è NULL, viene considerato privo del tipo di dati di base sottostante. Questa regola vale anche quando il valore Null deriva da una variabile o da una colonna a cui è associato un tipo di dati specifico.

    Nell'esempio seguente il valore di VariantCol è impostato su NULL e non vi è associato alcun tipo dati, anche se il valore Null deriva da una variabile int:

    DECLARE @IntVar int

    SET @IntVar = NULL

    UPDATE SomeTable SET VariantCol = @IntVar WHERE PriKey = 123

  • Nelle assegnazioni da oggetti sql_variant a un oggetto con un tipo di dati diverso, è necessario eseguire il cast esplicito del valore sql_variant al tipo di dati di destinazione. Quando un valore sql_variant viene assegnato a un oggetto con un tipo di dati diverso, le conversioni implicite non sono supportate.

  • Per compatibilità con gli altri tipi di dati, gli oggetti catalogo, ad esempio la funzione DATALENGTH, che indicano la lunghezza degli oggetti sql_variant indicano la lunghezza dei dati. La lunghezza dei metadati inclusi in un oggetto sql_variant non viene restituita.

  • Per le colonne sql_variant l'opzione ANSI_PADDING è sempre impostata su ON. Se i valori di tipo char, nchar, varchar, nvarchar o varbinary vengono assegnati da un'origine in cui l'opzione ANSI_PADDING è impostata su OFF, i valori non vengono riempiti con caratteri nulli.

  • L'aggiornamento di una colonna nel Sottoscrittore determina la modifica del tipo di base in un'altra colonna sql_variant. Tale concetto viene illustrato nella procedura seguente:

    1. Creare una pubblicazione o una sottoscrizione di cui è stato eseguito il merge. Nella tabella pubblicata devono essere presenti una colonna sql_variant e una colonna c1. Aggiungere dati alla colonna sql_variant. Il tipo di base dei dati è datetime.

    2. Dopo la sincronizzazione iniziale, il tipo di base nel Sottoscrittore è ancora datetime.

    3. Aggiornare la colonna c1 nel Sottoscrittore.

    4. I dati della colonna sql_variant nel server di pubblicazione sono stati modificati in datetime2.

sql_variant nelle tabelle

  • È possibile utilizzare colonne sql_variant in indici e chiavi univoche, a condizione che la lunghezza dei dati nelle colonne chiave non superi i 900 byte.

  • Le colonne sql_variant non supportano la proprietà IDENTITY. È tuttavia possibile utilizzare colonne sql_variant come chiavi esterne o primarie parziali.

  • Non è possibile utilizzare colonne sql_variant all'interno di una colonna calcolata.

  • È possibile utilizzare l'istruzione ALTER TABLE per impostare il tipo di dati sql_variant per una colonna con qualsiasi tipo di dati, ad eccezione di text, ntext, image, timestamp o sql_variant. Tutti i valori esistenti vengono convertiti in valori sql_variant il cui tipo di dati di base coincide con il tipo di dati applicato alla colonna prima dell'esecuzione dell'istruzione ALTER TABLE. Non è invece possibile utilizzare ALTER TABLE per la conversione del tipo di dati di una colonna sql_variant in un altro tipo di dati, in quanto le conversioni implicite dal tipo sql_variant ad altri tipi non sono supportate.

Regole di confronto

  • Non è possibile utilizzare la clausola COLLATE per assegnare regole di confronto a livello di colonna a una colonna sql_variant. I valori di tipo carattere, ovvero char, nchar, varchar e nvarchar, in una colonna sql_variant possono far parte di qualsiasi regola di confronto e una singola colonna sql_variant può includere valori di tipo carattere di regole di confronto miste.

  • Quando a un'istanza sql_variant viene assegnato un valore, vengono assegnati sia il valore dei dati che il tipo di dati di base dell'origine. Se il valore dell'origine include regole di confronto, vengono assegnate anche le regole di confronto. Se il valore dell'origine include un tipo di dati definito dall'utente, viene assegnato il tipo di dati di base del tipo di dati definito dall'utente e non direttamente il tipo di dati definito dall'utente. L'istanza sql_variant non eredita le regole o i valori predefiniti associati ai tipi di dati definiti dall'utente. Se un valore di una colonna con una proprietà IDENTITY viene assegnato a un'istanza sql_variant, sql_variant accetta il tipo di dati di base della colonna di origine, ma non eredita la proprietà IDENTITY. Non è corretto assegnare un valore text, ntext o image a un'istanza sql_variant. Le conversioni implicite sono supportate in caso di assegnazione di valori da oggetti con tipi di dati diversi a un oggetto sql_variant.

Confronti sql_variant

Le colonne sql_variant possono includere valori di regole di confronto e tipi di dati di base diversi. Per questo motivo, in fase di confronto di operandi sql_variant, vengono applicate regole particolari. Tali regole sono valide per le operazioni che implicano confronti, ad esempio:

  • Operatori di confronto di Transact-SQL

  • ORDER BY, GROUP BY

  • Indici

  • Funzioni di aggregazione MAX e MIN

  • UNION (senza ALL)

  • Espressioni CASE

Per l'esecuzione di confronti sql_variant, la gerarchia dei tipi di dati SQL Server viene suddivisa in gruppi di tipi di dati. Il gruppo sql_variant ha la precedenza maggiore.

Gerarchia dei tipi di dati

Gruppo di tipi di dati

sql_variant

sql_variant

datetime

Data e ora

smalldatetime

Data e ora

Float

Dati numerici approssimati

Real

Dati numerici approssimati

decimal

Dati numerici esatti

money

Dati numerici esatti

smallmoney

Dati numerici esatti

bigint

Dati numerici esatti

int

Dati numerici esatti

smallint

Dati numerici esatti

tinyint

Dati numerici esatti

bit

Dati numerici esatti

nvarchar

Unicode

nchar

Unicode

varchar

Unicode

char

Unicode

varbinary

Dati binari

binary

Dati binari

uniqueidentifier

Uniqueidentifier

Nei confronti sql_variant vengono applicate le regole seguenti:

  • Nei confronti tra due valori sql_variant con tipi di dati di base diversi appartenenti a gruppi di tipi di dati differenti, il valore appartenente al gruppo di tipi di dati che occupa un livello più alto nella gerarchia viene considerato il maggiore dei due valori.

  • Nei confronti di valori sql_variant con tipi di dati di base diversi appartenenti allo stesso gruppo, il valore del tipo di dati di base che appartiene a un gruppo di livello inferiore nella gerarchia viene convertito in modo implicito nell'altro tipo di dati, dopodiché viene eseguito il confronto.

  • Quando vengono confrontati i valori sql_variant dei tipi di dati char, varchar, nchar o varchar, la valutazione viene eseguita in base ai criteri seguenti: LCID, versione LCID, flag di confronto e ID di ordinamento. Per ognuno di questi criteri il confronto viene eseguito con valori integer e nell'ordine indicato.

In base a queste regole, i risultati ottenuti da confronti tra valori sql_variant possono essere diversi rispetto a quelli ottenuti da confronti tra valori con lo stesso tipo di dati di base.

Operando A

Operando B

Risultato confronto non-variant

Risultato confronto sql_variant

'123' char

111 int

A > B

B > A

50000 int

5E1 float

A > B

B > A

Poiché per i valori di gruppi di tipi di dati diversi è necessario eseguire il cast esplicito per potervi fare riferimento in predicati di confronto, è possibile osservare gli effetti dell'applicazione delle regole solo dopo aver ordinato i set di risultati in una colonna sql_variant. I valori inclusi nella tabella seguente sono esempi di regole relative alla precedenza dei tipi di dati.

Chiave primaria

VariantCol

1

50.0 (tipo di base float)

2

5000 (tipo di base int)

3

'124000' (tipo di base char(6))

Nella tabella seguente viene illustrato il risultato dell'istruzione SELECT * FROM VariantTest ORDER BY VariantCol ASC.

Chiave primaria

VariantCol

3

'124000' (tipo di base char(6))

2

5000 (tipo di base int)

1

50.0 (tipo di base float)

I valori inclusi nella tabella seguente sono esempi di regole relative alla precedenza tra regole di confronto diverse.

IntKey

VariantCol

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

4

17,5 (decimal)

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

7

1,2 (decimal)

Nella tabella seguente viene illustrato il risultato dell'istruzione SELECT * FROM CollateTest ORDER BY VariantCol ASC. Nella tabella sono inclusi i valori del gruppo di tipi di dati numerici esatti raggruppati e i valori varchar raggruppati all'interno delle rispettive regole di confronto.

IntKey

VariantCol

5

abc (varchar SQL_Latin1_General_CP1_CS_AS)

6

klm (varchar SQL_Latin1_General_CP1_CS_AS)

3

qrs (varchar SQL_Latin1_General_CP1_CS_AS)

2

abc (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

1

qrs (varchar SQL_Latin1_General_Pref_Cp1_CI_AS)

7

1,2 (decimal)

4

17,5 (decimal)

Funzioni e dati sql_variant

Le funzioni Transact-SQL seguenti supportano i parametri sql_variant e restituiscono un valore sql_variant quando viene specificato un parametro sql_variant:

COALESCE

MIN

MAX

NULLIF

Le funzioni seguenti supportano i riferimenti a variabili o colonne sql_variant e non utilizzano sql_variant come tipo di dati per i valori restituiti:

COL_LENGTH

DATALENGTH

TYPEPROPERTY

COLUMNPROPERTY

ISNULL

 

Le funzioni Transact-SQL seguenti non supportano i parametri sql_variant:

AVG

RADIANS

STDEV[P]

IDENTITY

ROUND

SUM

ISNUMERIC

SIGN

VAR[P]

POWER

 

 

Le funzioni CAST e CONVERT supportano il tipo di dati sql_variant.

La nuova funzione SQL_VARIANT_PROPERTY() può essere utilizzata per ottenere informazioni sui valori sql_variant, ad esempio tipo di dati, precisione o scala.

Altri elementi Transact-SQL e dati sql_variant

Le colonne sql_variant non sono supportate nel predicato LIKE.

Le colonne sql_variant non sono supportate in indici full-text. Non è possibile specificare colonne sql_variant in funzioni come CONTAINSTABLE e FREETEXTTABLE.

Le istruzioni Transact-SQL seguenti consentono di specificare il tipo di dati sql_variant nelle stesse posizioni della sintassi in cui vengono specificati altri tipi di dati integer.

  • ALTER PROCEDURE

  • ALTER TABLE

  • CREATE PROCEDURE

  • CREATE TABLE

  • DECLARE variable

I componenti di catalogo di SQL Server consentono di ottenere informazioni sulle colonne sql_variant.

Il risultato dell'espressione CASE è un valore sql_variant se una delle espressioni di input o di risultato restituisce un valore sql_variant. Il tipo di base sottostante del risultato corrisponde a quello dell'espressione che viene valutata come risultato in fase di esecuzione.

Gli operandi di operatori di concatenazione delle stringhe o numerici non possono essere sql_variant. Tramite il codice seguente viene, ad esempio, generato un errore:

SELECT VariantCol + @CharacterVar

FROM MyTable

Eseguendo tuttavia il cast dell'operando sql_variant, è possibile eseguire l'operazione:

SELECT CAST(VariantCol AS varchar(25)) + @CharacterVar

FROM MyTable

Applicazioni e dati sql_variant

Se un'applicazione richiede un set di risultati in cui una colonna specifica restituisce dati sql_variant di un singolo tipo di dati di base sottostante, nell'applicazione è possibile utilizzare le funzioni CAST o CONVERT in istruzioni Transact-SQL per restituire dati sql_variant utilizzando il tipo di dati di base sottostante. In questo caso, nell'applicazione i dati vengono gestiti come una colonna di set di risultati del tipo di dati di base sottostante.

Il provider OLE DB SQL Server Native Client per SQL Server introduce il tipo OLE DB specifico del provider DBTYPE_SQLVARIANT, da utilizzare con parametri e colonne sql_variant.

Il driver ODBC SQL Server Native Client per SQL Server introduce il tipo di dati di database ODBC specifico del provider SQL_SS_VARIANT, da utilizzare con parametri e colonne sql_variant.

In SQL Server i valori sql_variant vengono convertiti in nvarchar(4000) quando si utilizzano applicazioni connesse tramite le interfacce seguenti:

  • Provider OLE DB per SQL Server 7.0.

  • Driver ODBC per SQL Server di SQL Server 7.0.

Se la lunghezza della stringa ottenuta è maggiore di 4000 caratteri, tramite SQL Server vengono restituiti i primi 4000 caratteri.

In SQL Server i valori sql_variant vengono convertiti in varchar(255) quando si utilizzano applicazioni connesse tramite le interfacce seguenti:

  • Driver ODBC per SQL Server di SQL Server versione 6.5 o versioni precedenti.

Se la lunghezza della stringa ottenuta è maggiore di 255 caratteri, tramite SQL Server vengono restituiti i primi 255 caratteri.