Floating Point - Precision Of Arithmetic Calculations On Server
Dec 4, 2013
I am trying to understand why SQL Server gives me significantly lower precision than many of the other sources that have tried when using the POWER function. My environment is 2008 R2 SP2 (10.50.4000.0 X64, Standard edition) on Windows 2008 X64 SP1
-- using results from other sources (c#, windows calc, casio.com)
SELECT POWER((1+3.33272237835747E-05),12) -- 1.0004
SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004
SELECT POWER((1+3.332722378349525584658E-5),12) -- 1.0004
It is not important what I am trying to do, but in case it will work, I am trying to calculate the monthly return for a 90-day T-bill given the compounded annual return. x is the annualized return.
I can't see any reason for this error, not having a high level understanding of maths I thought I'd post it and hope someone could share some light on it.
I yesterday got called by a client who said that a payment for £15 + VAT was being passed to their payment gateway as 17.62 when it should be 17.63. The VAT calculation is performed in a SQL Server 2000 stored procedure. In the end I tracked it down and it wasn't a propblem with my calculation.
The price was coming out as 17.63 fine. The stored procedure then had to return this price in pence (17.63 * 100 = 17.63). When I put in a print statement with this calculation it was correct but when I output the variable that the result was assigned to it was coming out as 1762.
The variable that the result was being put into was of real datatype.
I then wrote a udf to test this. Here is the function:
CREATE FUNCTION dbo.POUNDS_TO_PENCE ( @POUNDVALUE real ) RETURNS INTEGER AS BEGIN
RETURN @POUNDVALUE * 100
END
As you can see nothing very special.
If you run this runction and pass in 17.63 it will return 1762!!!
The bit I don't get is if I change the @POUNDSVALUE intput variable to type float it returns the correct amount.
I've also found that the same problem occurs when passing in £30 + VAT (35.25) + 1pence. So, 35.26 comes out as 3525 instead of 3526. This is the case if you keep doubling the number (and adding a few pence here and there).
Does anyone know why this is or is it a bug in the processor?
The SQL books online say the following about the float and real data types:
-------------------------------------------------------- float and real (T-SQL) Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.
Syntax float[(n)] Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.
n is Precision Storage size 1-24 7 digits 4 bytes 25-53 15 digits 8 bytes
The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).
real Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).
Hi,I would like to know, if I need to do some floting point operations(mainly multiplication and division) on each roll of a table, should Iread the data out from the DB and do the calculation with a programminglanguage, say C#, or should I just use sql to do it on the sql server.An obvious advantage of doing it in the sql server is that you dontneed to transfer the data between the sql server and he applicationserver.But I am not sure if there are any other factors that will overridethis advantage: like the performance of doing lots of floting pointoperations in sql server.How is the performance of doing floting point operations in sql servercomparing to C# or other languages?Also are there any other factors that should be considered for thiscase andare there any other advantages to do this is sql server or in c#?ThanksBenny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I've got a price in euro as a string, which I can easily cast to a numeric SSIS data type e.g. R4, R8, DECIMAL, NUMERIC. And I've got the dollar/euro exchange rate stored in an SSIS variable of type DOUBLE, set to 1.28 for testing purposes. I want to multiply the two values and return the (dollar) result, rounded (not truncated) to 2 decimal places, as a string.
Here are some experiments I did in an SSIS expression editor:
(DT_WSTR, 10) (1.28 * 31.10) evaluates to "39.8080"
(DT_WSTR, 10) (1.28 * (DT_R8) "31.10") evaluates to "39.808"
(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 0) "31.10") evaluates to "39.68"
(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 1) "31.10") evaluates to "39.808"
(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 2) "31.10") evaluates to "39.8080"
(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 3) "31.10") evaluates to "39.80800"
Of course, what I really want is "39.81", so I went on:
I got below error in the SQL Server Production Server and i checked in the microsoft site it needs to install SQL Server service pack 4 to resolve the problem.
"A floating point exception occurred in the user process. Current transaction is canceled"
I need help that i want to reproduce this below problem in the SQL Server environment and tried several ways but no luck.
I need to write a floating point value to a column in a SQL server database. I have the following line ofcode, that formats the float value before passing it off to the stored procedure. Please look at the line of code below. Does the 8 after SqlDbType.Foat mean that this will be a floatingpoint with 8 signigicant digits past the decimal point? mySqlCommand.Parameters.Add("@Price", SqlDbType.Float, 8).Value = float.Parse(InitialPrice);
Intermittently, the following error has been displayed when a SQL job on our box (SQL Server 2000 Service Pack 4 running on Windows 2000) runs.
'A floating point exception occurred in the user process. Current transaction is canceled.' The error number given is 3628 though I've also seen a 4xxx number (not at machine at the moment so cant be precise for the latter).
The intermittent problem over the last two days has become more regular and now does not appear to be related solely to the SQL job. For example, when connecting to the box using my local copy of SQL 2005 Management Studio and I attempt to browse the database objects I get this problem.
To the best of my knowledge, this server has not had any software installed on it and/or nothing has changed on it recently.
It has now got to the point that this particular job no longer runs. The job in question, in case this is significant, is a vanilla stored proc that returns data using OPEN ROWSET to communicate with an Access database (this connectivity has been verified to be okay, in that other similar jobs all run just fine)
If anybody can shed some light and/or point me in the right direction I would be extremely grateful.
I'm trying to perform a calculation on a field in SQL Server thatshould return a value with a decimal point. My problem is that thevalue returned is truncated without the decimal point. Is there asetting that needs to be turned on in SQL server to allow this?for exampleSelect 20/3should return 6.6666667but instead I get 6
Hi SQL people, I have rating system on pages in my website, each page being rated one to five by users. At the back end, an ASP.NET page displays the average rating and number of ratings for each page. As the rating is stored as an integer, the SQL statement returns an integer average. I would like to get a floating point average. Currently the statement used to return the average looks like this:
select AVG(Rating) as AverageRating from Ratings where [RatingPage] = @RatingPage
Is there a simple way to modify this to return a floating point average without iterating through the records, or converting the Rating field to a float?
I've a SQL view performing a number of very precise calculations ie.495/(1.112 - (.00043499*((Col1 + Col2 + Col3))) + ( .00000055 * ((Col1+ Col2 + Col3)) * ((Col1 + Col2 + Col3))) - (.00028826 * Col4))) -450)/100Obviously this sometimes causes a precision issues because the floatand real datatypes don't hold precise values... My problem is thatconverting to the numeric or decimal datatype easily produces an errorif the precision is not big enough to hold the resulting output... Sohere's my questions:Does anyone see a problem with me maxing out the precision of thedecimal datatype (ie. use DECIMAL(38,8) across a couple dozeninter-related formulas?So the above code would now exec like this:495/(CONVERT(DECIMAL(38,8),1.112) -(CONVERT(DECIMAL(38,8),.00043499)*((Col1 + Col2 + Col3))) +(CONVERT(DECIMAL(38,8),.00000055) * ((Col1 + Col2 + Col3)) * ((Col1 +Col2 + Col3))) - (CONVERT(DECIMAL(38,8),.00028826) * Col4))) - 450)/100If anyone does see a problem with this approach, could you suggestanother alternative?
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT) SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
System.Data.SqlClient.SqlException: A floating point exception occurred in the user process. Current transaction is canceled. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at ...etc etc
when updating one particular table, but not when updating another using nearly the same code.
I've searched and searched on the inet, but with no joy.
Microsoft have several different manifestations of it, under various different circumstances, but the solutions all seem to involve installing SP4, which by the looks of it is a massive procedure that you have to go to university to be competent to do.
In any case, according to Enterprise Manager, I already have SP4 included. On the other hand, the one file of mine I checked the date of against MS's hotfix file list was 2002 not 2003, so who knows?
My Service Manager is v 8.00.760, and it's the Development Edition (ie it's all running on the one machine).
According to the info on sqlDataAdaptor.update, the sqlError that gets returned as part of the sqlException contains fields Class, which gives the seriousness of the error, and State, which identifies the exact error. I get Class=16 (user-fixable) and State=1, which we have to go to SQL Books Online to discover the meaning of. SQL BO probably sounded like a good idea to W.Gates, but it took me half an hour to get anywhere near finding out what that '1' meant. When I did, it was (guess): 'A floating point exception occurred in the user process. Current transaction is canceled.' !!!
Can anyone please tell me what exactly might be causing the error?
sql server 2000 sp4 build 2184runs stats with maintenance plansp_msforeachtablestored procedure updating full stats for each table and the resultsare identicalA floating point exception occurred in the user process. Currenttransaction is canceled.all pointers are to move to latest service packAny pointers on how to avoid this errorThanks for your timeMassa
I am trying to run "select * from <table> where <columnname> = 0.0" inquery analyzer<columnname> is a floatI get the following error:Server: Msg 3628, Level 16, State 1, Line 1A floating point exception occurred in the user process. Currenttransaction is canceled.I get the same error when I use the float <columnname> through anapplicationI have tried upgrading to SP4, without any luck - still get the errormessage.Any help would indeed be appreciated.Thanks -AH
We get the following error message."a floating point exception occured in the user process. currenttransaction is cancelled".this message comes when trying to excute a stored procedure. Thisexception is unpredictable.OS : Windows 2000 (SP3)Version: SQL server 2000 (SP3).
Hi,I'm running SQL Server Version 8.00.194 on Windows 2000.I am am running this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'and TheoVolImplied > 0.0TheoVolImplied is of type float, precision 15, length 8.When I run this query I get this error:Server: Msg 3628, Level 16, State 1, Line 1A floating point exception occurred in the user process. Currenttransaction is canceled.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere TheoVolImplied > 0.0It works fine with no problems.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'It works fine with no problems.Anyone have any ideas about what might be wrong?
I'm running SQL Server 2005 on a Server 2003 machine serving both our home network as well as a remote site through a point-to-point T1. While file transfer speeds are up to par, the remote site's interaction with SQL Server (Point of sale system) is very slow. After testing I am certain that it has nothing to do with the actual physical machine in place neither is it an issue with the program itself since speeds are as they are supposed to be over the home network lan. It seems that there might be a packet size issue or something of the sort. Has anyone dealt with this before or have any thoughts?
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
We have a view in a 9205 oracle database. We can query fine and the decimal precision is there. When we query this same view from ms sql server we lose the precision so 115.25 becomes 115. does anyone know a workaround for this?
In SQL Server I've created a linked server to an Oracle database. I am trying to insert (within the context of an sql server table trigger) an SQL Server datetime to an Oracle column with similar precision. Oracle timestamps are not compatible with sql server datetimes and I don't know how to convert the data (or if I should use a different type of column to store the data in Oracle). I have full control over the structure of the Oracle table so I can use a different type if timestamp is not best, but I need the destination column to have at least the same precision as the sql server datetime value. What is the easiest way to do this?
I have the following code in a SP. The 2 if statements for the @@ Error I added only for testing as I suddenly started get the following error at the 1st @@Error statement
Msg 232, Level 16, State 2, Line Arithmetic overflow error for type varchar, value = 10000.00000.
I cannot have this SP running minutes it must be done in seconds.
I have tried the folloiwng changes but then it takes to long.
I am looking for some help on how to do a calculation for each row returned in a query.
For example, I have the following query:
Select Name, Supplier, Colour, Cost, [Cost Inc Vat] from products;
The idea is that Cost and a variable, @vat included in part of the SQL statement (I assume), will be be used to calculate the value of the column [Cost Inc Vat] for each row, but I dont know how to do this.
Is is possible in SQL Server Compact and how can it be done?
promo_ID: the id of the web form that site visitors filled out to send us their information PPC_source: If this lead is a result of a Pay-per-click campaingwe capture the name of the search engine Status_ID: Shows if the lead has been contacted, quoted, sold, bad lead, etc. I was asked to come up with a summary report calculating the number of leads under each status ID - for each PPC_source Example:
Total leads |
Google |
Yahoo
Status = pending
Status = contacted
Status = quoted
Status = Sold
Looping over each record (12,000 and counting) to get the sums of each status for each pay-per-click campaign takes a while (not all campaigns / status are listed above). The question: Is it better to retrieve all records in a dataset and do the math in the business layer or is there a way to do the math in a SQL Procedure, or function. Thanks.
Is there a formula for calculating how expensive a transaction will be in terms of disk space used before its run. I dont want it accurate to the MB, but rough enough so I can determine how much additional space to assign to a transaction log or SAN volume.
Currently we're reindexing ~25billion rows, nothing too wide, say 12 columns consisting of 1 varchar(50) and the rest ints, bits and money.Roughly speaking if I reindex the clustered index on an int indetity, (with sort_in_tempdb) how would I calculate the the disk space used?
We've been using SQL Server 2005 for a while as the db for our web app. Everything has been working fine, until yesterday when we started getting a "Arithmetic operation resulted in an overflow. (System.Data)" error message when trying to connect from SQL Server Management Studio or from our web app. This only happens when trying to connect remotely, although remote connections have worked for us perfectly in the past. The full error message is reproduced below. Thanks ahead of time for any help. ===================================Cannot connect to serverName===================================Arithmetic operation resulted in an overflow. (System.Data)------------------------------Program Location: at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."
The table is set to nvarchar, and i am just trying to make the prices go up 10%.
OK, so I have this query where I'm trying to subtract values like this, when I do this I am getting (Arithmetic overflow error converting varchar to data type numeric.) I have tried many different things, and now of these work, it'll either return 0 because it loses the .XXXXX.
Convert(DECIMAL(10,7),CAST([TIME_OF_COMPLETION] as DECIMAL(10,7)) - Convert(DECIMAL(10,7),CAST([OPR_DELIVERED_TIME] as DECIMAL(10,7)) round(cast(cast(hist.[TIME_OF_COMPLETION] AS float) as DECIMAL(15, 5)) - CAST(hist.[OPR_DELIVERED_TIME] AS FLOAT),1 SELECT convert(FLOAT,CAST('735509.00053' AS DECIMAL(10,5))) - convert(FLOAT,CAST('735509.00047' AS DECIMAL(10,5)))
I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -
SELECT Column_1, Column_2, Column_3, 10*Column_1 AS Column_4, 10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1 First 3 Columns are available within the Original Table_1 The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.