Conversione del tipo di dati (Motore di database)
I tipi di dati possono essere convertiti negli scenari seguenti:
Quando i dati di un oggetto vengono spostati, confrontati o combinati con i dati di un altro oggetto, può essere necessario convertirli nel tipo di dati del secondo oggetto.
Quando i dati di una colonna di risultati, di un codice restituito o di un parametro di output di Transact-SQL vengono spostati in una variabile di programma, è necessario convertirli dal tipo di dati di sistema di SQL Server a quello della variabile.
I tipi di dati possono essere convertiti in modo implicito o esplicito:
Le conversioni implicite non sono visibili all'utente.
SQL Server converte automaticamente i dati da un tipo all'altro. Se ad esempio un valore smallint viene confrontato con un valore int, il valore smallint viene implicitamente convertito in un valore int prima dell'esecuzione del confronto.
Nelle conversioni esplicite vengono utilizzate le funzioni CAST e CONVERT.
Le funzioni CAST e CONVERT consentono di convertire un valore, ad esempio una variabile locale, una colonna o un'altra espressione, da un tipo di dati all'altro. Ad esempio, la funzione CAST seguente converte il valore numerico $157.27 nella stringa di caratteri '157.27':
CAST ( $157.27 AS VARCHAR(10) )Utilizzare CAST invece di CONVERT per rendere il codice di programma Transact-SQL compatibile con lo standard ISO. Utilizzare CONVERT invece di CAST per trarre vantaggio dalla funzionalità degli stili disponibile in CONVERT.
Alcune conversioni implicite ed esplicite non sono supportate quando si converte il tipo di dati di un oggetto SQL Server in un altro tipo di dati. Non è ad esempio possibile convertire un valore nchar in un valore image. Un valore nchar può essere convertito in un valore binary solo mediante una conversione esplicita, in quanto la conversione implicita in valore binary non è supportata. È tuttavia possibile convertire in modo esplicito e implicito un valore nchar in valore nvarchar.
Nel caso del tipo di dati sql_variant, SQL Server supporta la conversione implicita nel tipo sql_variant degli oggetti a cui è associato un tipo di dati diverso. SQL Server non supporta tuttavia le conversioni implicite dal tipo di dati sql_variant in oggetti a cui è associato un tipo di dati diverso.
Per ulteriori informazioni sulle conversioni supportate tra oggetti di SQL Server, vedere CAST e CONVERT (Transact-SQL).
Le conversioni supportate tra una variabile di applicazione e una colonna del set di risultati, un codice restituito, un parametro o un indicatore di parametro di SQL Server vengono definite dall'API di database. Per ulteriori informazioni, vedere Spostamento dei dati in variabili di applicazione.
Funzionamento della conversione dei tipi di dati
Nelle sezioni seguenti di questo argomento viene descritto il funzionamento della conversione dei tipi di dati seguenti:
dati di tipo binary e varbinary |
dati di tipo money |
dati di tipo bit |
dati di tipo decimal e numeric |
dati di tipo carattere |
tipi di dati mediante stored procedure di automazione OLE |
dati relativi alla data e all'ora |
dati integer |
dati di tipo float e real |
Conversione dei dati di tipo binary e varbinary
Nella conversione da un tipo di dati string (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext o image) a un tipo di dati binary o varbinary di lunghezza variabile, SQL Server esegue il riempimento o il troncamento dei dati a destra. Nella conversione di altri tipi di dati nel tipo binary o varbinary, viene eseguito il riempimento o il troncamento dei dati a sinistra. Il riempimento viene eseguito utilizzando zero esadecimali.
La conversione nei tipi di dati binary e varbinary è utile quando i dati binary risultano i dati più semplici da spostare. Quando un valore di qualsiasi tipo viene convertito in un valore binary di dimensioni sufficienti e successivamente riconvertito nel tipo dati iniziale, si ottiene sempre lo stesso valore se entrambe le conversioni vengono eseguite nella stessa versione di SQL Server. La rappresentazione binaria di un valore può variare da una versione all'altra di SQL Server.
La conversione del tipo di dati int, smallint o tinyint nel tipo di dati binary o varbinary è supportata. Se è stato eseguito il troncamento e il valore binary viene riconvertito in un valore integer, il valore ottenuto sarà diverso dal valore integer originale. Ad esempio, l'istruzione SELECT seguente indica che il valore integer 123456 viene in genere archiviato nel formato binario 0x0001e240:
SELECT CAST( 123456 AS BINARY(4) )
L'istruzione SELECT seguente illustra il troncamento automatico delle cifre iniziali se il valore di destinazione binary è troppo piccolo per l'archiviazione dell'intero valore, in modo che lo stesso numero possa essere archiviato sotto forma di 0xe240:
SELECT CAST( 123456 AS BINARY(2) )
Il batch seguente illustra come il troncamento automatico possa influire sulle operazioni aritmetiche senza tuttavia generare errori:
DECLARE @BinaryVariable2 BINARY(2)
SET @BinaryVariable2 = 123456
SET @BinaryVariable2 = @BinaryVariable2 + 1
SELECT CAST( @BinaryVariable2 AS INT)
GO
Il risultato finale è 57921, non 123457.
[!NOTA]
È possibile che le conversioni da un tipo di dati ai tipi di dati binary e viceversa eseguite in versioni diverse di SQL Server producano risultati diversi.
Conversione in dati di tipo bit
Nelle conversioni nel tipo di dati bit i valori diversi da zero diventano uguali a 1.
Conversione dei dati di tipo carattere
Se un'espressione di caratteri viene convertita in un tipo di dati carattere di dimensioni diverse, i valori troppo lunghi per il nuovo tipo di dati vengono troncati.
Se un'espressione di caratteri viene convertita in un'espressione di caratteri con tipo di dati o dimensioni diverse, ad esempio da char(5) a varchar(5), oppure da char(20) a char(15), al valore convertito vengono assegnate le regole di confronto del valore di input. Se un'espressione non di caratteri viene convertita in dati di tipo carattere, al valore convertito vengono assegnate le regole di confronto predefinite del database corrente. In entrambi i casi è possibile assegnare regole di confronto specifiche mediante la clausola COLLATE.
[!NOTA]
Le conversioni di tabella codici sono supportate per i tipi di dati char e varchar, ma non per il tipo di dati text. Come nel caso delle versioni precedenti di SQL Server, la perdita di dati durante le conversioni di tabella codici non viene segnalata.
Le espressioni di caratteri che vengono convertite in un tipo di dati numeric approssimato possono includere una notazione esponenziale facoltativa (una e minuscola o maiuscola seguita da un segno più (+) o meno (-) facoltativo e quindi da un numero).
Le espressioni di caratteri che vengono convertite in un tipo di dati numeric esatto devono includere cifre, il separatore decimale e un segno più (+) o meno (-) facoltativo. Gli spazi vuoti iniziali vengono ignorati. L'utilizzo della virgola come separatore, ad esempio come separatore decimale nel numero 123.456,00 non è consentito.
Le espressioni di caratteri che vengono convertite nel tipo di dati money o smallmoney possono includere inoltre un separatore decimale e il simbolo di dollaro ($) facoltativi. L'utilizzo della virgola come separatore decimale, ad esempio $ 123.456,00, è consentito.
Nell'esempio seguente viene illustrata la conversione di dati per la visualizzazione. I dati relativi alle vendite vengono convertiti in dati di tipo carattere prima del confronto delle stringhe e la data corrente viene convertita nel formato 3, dd/mm/yy.
USE AdventureWorks;
GO
SELECT SalesPersonID,
CAST(SalesYTD AS varchar(12)),
CONVERT(VARCHAR(12), GETDATE(), 3)
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%'
GO
Nell'esempio seguente un valore uniqueidentifier viene convertito in un tipo di dati char.
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'char'
GO
Nell'esempio seguente la data corrente viene convertita nel formato 3, dd/mm/yy.
SELECT CONVERT(char(12), GETDATE(), 3)
GO
Conversione dei dati relativi alla data e all'ora
Nelle conversioni dei tipi di dati date e time, in SQL Server vengono rifiutati tutti i valori non riconosciuti come date o orari. Per una panoramica di tutti i tipi di dati e delle funzioni di data e ora Transact-SQL, vedere Funzioni di data e ora (Generatore report 1.0).
Nell'esempio seguente i valori date e datetime2 vengono rispettivamente convertiti nei tipi di dati varchar e binary.
DECLARE @mydate date
SET @mydate = '4/05/98'
SELECT CAST(@mydate AS varchar) AS DATE_VARCHAR
GO
DECLARE @mydate datetime2
SET @mydate = '4/05/98'
SELECT CAST(@mydate AS binary) AS DATE_BINARY
GO
Set di risultati:
(1 row(s) affected)
DATE_VARCHAR
------------------------------
Apr 5 1998
(1 row(s) affected)
DATE_BINARY
--------------------------------------------------------------
0x0700000000008B210B
(1 row(s) affected)
Conversione dei dati di tipo float e real
I valori di tipo float vengono troncati in fase di conversione in un tipo di dati integer.
Durante la conversione dal tipo di dati float o real in dati di tipo carattere, risulta più utile utilizzare la funzione per i valori stringa STR anziché la funzione CAST( ). STR infatti garantisce un maggiore controllo sul formato. Per ulteriori informazioni, vedere STR (Transact-SQL) e Funzioni (Transact-SQL).
Conversione dei dati di tipo money
Nella conversione di dati integer nel tipo di dati money le unità vengono interpretate come unità di valuta. Ad esempio, il valore integer 4 viene convertito nell'equivalente di 4 unità di valuta per il tipo money.
Nell'esempio seguente i valori smallmoney e money vengono rispettivamente convertiti nei tipi di dati varchar e decimal.
USE AdventureWorks;
GO
DECLARE @mymoney_sm smallmoney;
SET @mymoney_sm = 3148.29;
SELECT CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar';
GO
DECLARE @mymoney money;
SET @mymoney = 3148.29;
SELECT CAST(@mymoney AS decimal) AS 'MONEY DECIMAL';
GO
Set di risultati:
SM_MONEY VARCHAR
------------------------------
3148.29
(1 row(s) affected)
MONEY DECIMAL
----------------------
3148
(1 row(s) affected)
Conversione dei dati di tipo decimal e numeric
In SQL Server per i tipi di dati decimal e numeric ogni combinazione specifica di precisione e scala viene considerata un tipo di dati diverso. decimal(5,5) e decimal(5,0) sono ad esempio considerati tipi di dati diversi.
Nelle istruzioni Transact-SQL una costante con separatore decimale viene convertita automaticamente in un valore di tipo numeric in base ai valori di precisione e scala minimi necessari. Ad esempio, la costante 12,345 viene convertita in un valore numeric con precisione 5 e scala 3.
La conversione dal tipo di dati decimal o numeric nel tipo di dati float o real può comportare una diminuzione della precisione. La conversione dal tipo di dati int, smallint, tinyint, float, real, money o smallmoney nel tipo di dati decimal o numeric può causare un overflow.
Per impostazione predefinita, in SQL Server, nelle conversioni in un valore decimal o numeric con precisione e scala inferiori viene applicato l'arrotondamento. Se l'opzione SET ARITHABORT è impostata su ON, in caso di overflow viene segnalato un errore in SQL Server. La diminuzione di precisione e scala nelle operazioni di conversione non è sufficiente per generare un errore.
Conversione di dati integer
Quando i numeri interi vengono convertiti implicitamente in un tipo di dati carattere, se il numero intero è troppo grande per adattarsi al campo del carattere, SQL Server immette il carattere ASCII 42, l'asterisco (*).
Le costanti integer maggiori di 2.147.483.647 vengono convertite nel tipo di dati decimal, non nel tipo di dati bigint. Nell'esempio seguente viene illustrata la modifica del tipo di dati del risultato da int a decimal quando il valore della soglia viene superato.
SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ;
Set di risultati:
Result1 Result2
1073741823 1073741824.500000
Conversione del tipo di dati mediante stored procedure di automazione OLE
Poiché in SQL Server vengono utilizzati tipi di dati Transact-SQL e nell'automazione OLE vengono utilizzati tipi di dati Visual Basic, i dati che vengono trasferiti da un sistema all'altro devono essere convertiti tramite le stored procedure di automazione OLE.
Nella tabella seguente vengono descritte le conversioni dei tipi di dati di SQL Server nei tipi di dati Visual Basic.
Tipo di dati di SQL Server |
Tipo di dati di Visual Basic |
|---|---|
char, varchar, text, nvarchar, ntext |
String |
decimal, numeric |
String |
bit |
Boolean |
binary, varbinary, image |
Matrice di Byte() unidimensionale |
int |
Long |
smallint |
Integer |
tinyint |
Byte |
float |
Double |
real |
Single |
money, smallmoney |
Currency |
datetime, smalldatetime |
Date |
Qualsiasi tipo impostato su NULL |
Variant impostato su Null |
Tutti i valori di SQL Server vengono convertiti in un unico valore Visual Basic, ad eccezione dei valori binary, varbinary e image. Questi valori vengono convertiti in matrice di Byte() unidimensionale in Visual Basic. La matrice ha un intervallo di Byte(0 to length1), dove length è il numero di byte incluso nel valore SQL Serverbinary, varbinary o image.
Di seguito sono riportate le conversioni dai tipi di dati Visual Basic nei tipi di dati di SQL Server.
Tipo di dati di Visual Basic |
Tipo di dati di SQL Server |
|---|---|
Long, Integer, Byte, Boolean, Object |
int |
Double, Single |
float |
Currency |
money |
Date |
datetime |
String con 4000 caratteri o meno |
varchar/nvarchar |
String con più di 4000 caratteri |
text/ntext |
Matrice di Byte() unidimensionale con numero di byte minore o uguale a 8000 |
varbinary |
Matrice di Byte() unidimensionale con un numero di byte maggiore di 8000 |
image |