UGISS User Group Italiano SQL Server
In questo articolo parleremo della situazione che si verifica quando nel DB vengono memorizzati dei valori numerici, in colonne di tipo float, che però non possono più essere letti correttamente.
Tutto è iniziato dalla segnalazione di un utente che lamentava un errore in fase di estrazione dei dati di fatturato. La versione di SQL Server in produzione è la 2000 (SP4 – versione 8.00.2039) e l’errore restituito durante l’esecuzione della query è: "An error occurred while executing batch. Error message is: Overflow aritmetico."
Dopo aver fatto alcune prove ho isolato la colonna che produce l’errore, è una colonna di tipo float in cui SQL Server ha memorizzato un valore non valido, si tratta del valore indefinito NaN (Not a Number) che è stato possibile memorizzare ma che ora non è più possibile leggere in modo tradizionale con una di questo tipo:
select
fieldfloat
from
dbo.tab_with_NaN
L’unico modo che ho trovato per leggere i dati è attraverso una conversione dal tipo float al tipo varchar:
select
left(fieldfloat, 100) as fieldfloat
from
dbo.tab_with_NaN
Con SQL Server 2000 (SP4) è possibile riprodurre la situazione come descritto di seguito.
Creazione tabella di test dbo.tab_with_NaN:
use [TestDB];
go
create table dbo.tab_with_NaN
(
id int identity(1, 1) not null,
fieldfloat float
primary key(id)
);
go
Per inserire una riga valorizzando la colonna fieldfloat con il valore indefinito NaN si può utilizzare il seguente codice C# (lo stesso ragionamento è valido per i valori –Infinito (NegativeInfinity) e +Infinito (PositiveInfinity):
private void button1_Click(object sender, EventArgs e)
{
double dNot_a_Number = double.NaN;
// For NegativeInfinity
//dNot_a_Number = double.NegativeInfinity;// For PositiveInfinity
//dNot_a_Number = double.PositiveInfinity;// Connection
SqlConnection conn = new SqlConnection(@"Data Source=<server_name>;Initial Catalog=<database_name>;Persist Security Info=True;User ID=<user_name>");SqlCommand cmd = new SqlCommand("Insert tab_with_NaN(fieldfloat) Values(@val)", conn);
cmd.Parameters.AddWithValue("@val", dNot_a_Number);
conn.Open();
// Insert Not a Number
int rows = cmd.ExecuteNonQuery();conn.Close();
}
Se tentiamo la lettura dei dati con la query:
select
*
from
dbo.tab_with_NaN
Otteniamo l’errore: "An error occurred while executing batch. Error message is: Overflow aritmetico." Se tentiamo invece la lettura applicando alla colonna fieldfloat la conversione implicita a varchar otteniamo:
select
left(fieldfloat, 100) as fieldfloat
from
dbo.tab_with_NaN
Per sistemare i dati (recuperando lo storico) è sufficiente realizzare un’opportuna query di UPDATE (nel mio caso, fortunatamente 
, è stato possibile identificate in maniera semplice la logica di ricostruzione del valore numerico).
Dopo aver sistemato i dati si è voluto rafforzare l’integrità di dominio del DB definendo una regola per impedire la memorizzazione di valori indefiniti (o comunque fuori dal range del tipo di dato float) nella colonna floatfield, anche perchè questi valori non potranno essere letti correttamente. Per fare questo si utilizza un CONSTRAINT di tipo CHECK verificato attraverso la funzione (user-defined function) dbo.udf_SS2000_float_isnan:
— dbo.udf_SS2000_float_isnan
if object_id(‘udf_SS2000_float_isnan’) is not null
drop function dbo.udf_SS2000_float_isnan;
go
create function dbo.udf_SS2000_float_isnan(@value float) returns smallint
as
begin
/*
Descrizione: User-defined function di controllo dei valori float
*/
declare
@result smallint
set
@result = -1
select
@result = 0
where
((@value between -1.79E+308 and -2.23E-308) or
(@value = 0) or
(@value between 2.23E-308 and 1.79E+308))
return @result
end
go
alter table
dbo.tab_with_NaN
with nocheck add constraint
chk_fieldfloat_NaN check (dbo.udf_SS2000_float_isnan(fieldfloat)=0)
Con SQL Server 2005 non sono riuscito a riprodurre il problema (nel mio caso è meglio così ), il DB, anche senza CHECK CONSTRAINT, impedisce il salvataggio di valori indefiniti nella colonna floatfield.
Conclusioni
Con SQL Server 2005 Microsoft non supporta i valori NaN, +Infinito e –Infinito come valori numerici memorizzabili nelle colonne di tipo float.
Qualcuno però ha già richiesto una modifica … date un’occhiata a questa segnalazione su connect: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674
Limitare l’utilizzo del tipo di dato float solo nei casi strettamente necessari, la situazione anomala che ho riscontrato su SQL Server 2000 non si sarebbe verificata se la colonna floatfield fosse stata di tipo decimal().
Pulizia DB
use [TestDB];
go
if object_id(N’tab_with_NaN’, N’U’) is not null
drop table dbo.tab_with_NaN;
go
— dbo.udf_SS2000_float_isnan
if object_id(‘udf_SS2000_float_isnan’) is not null
drop function dbo.udf_SS2000_float_isnan;
