My platform is SQL Server 2005. One of the tables of my db has
28589928 rows and one of the fields is real. When I backup and restore
this database to another server (using the SQL Server internal tool)
and do a SELECT SUM(<<field name>>) from <<table name>>, the results
from these two servers are slightly different. I don't understand the
reason because these databases are static and they should be exactly
the same.

Then when I tried to run the above select on the same server a few
times in a row, I also had inconsistent results (and no user is
accessing the database). This doesn't make any sense to me...

The FLOAT/REAL data type is approximate, and as such not all values in
the data type range can be represented exactly.

Interesting readings on the topic:

http://blogs.msdn.com/khen1234/archi...13/417153.aspx
http://sqlblog.com/blogs/hugo_kornel...all-exact.aspx
http://msdn.microsoft.com/en-us/library/ms173773.aspx

--
Plamen Ratchev
http://www.SQLStudio.com

In addition to Plamen's reply (or maybe it is also stated in the linked
pages), the exact value of approximate data types such as real depend on
the specific CPU of the specific server. If the two servers do not have
the exact same CPU(s), then you can get differences, even without
SUMming them.

Also note, that if you SUM both small and large numbers, then you could
loose precision. If you do, then the result can differ depending on the
order in which the numbers are added up, which (theoretically) can be
different each time you run the query.

--
Gert-Jan
SQL Server MVP


rnhuch wrote:
>
> My platform is SQL Server 2005. One of the tables of my db has
> 28589928 rows and one of the fields is real. When I backup and restore
> this database to another server (using the SQL Server internal tool)
> and do a SELECT SUM(<<field name>>) from <<table name>>, the results
> from these two servers are slightly different. I don't understand the
> reason because these databases are static and they should be exactly
> the same.
>
> Then when I tried to run the above select on the same server a few
> times in a row, I also had inconsistent results (and no user is
> accessing the database). This doesn't make any sense to me...

On Sep 26, 12:19pm, Gert-Jan Strik <so...@toomuchspamalready.nl>
wrote:
> In addition to Plamen's reply (or maybe it is also stated in the linked
> pages), the exact value of approximate data types such as real depend on
> the specific CPU of the specific server. If the two servers do not have
> the exact same CPU(s), then you can get differences, even without
> SUMming them.
>
> Also note, that if you SUM both small and large numbers, then you could
> loose precision. If you do, then the result can differ depending on the
> order in which the numbers are added up, which (theoretically) can be
> different each time you run the query.
>
> --
> Gert-Jan
> SQL Server MVP
>
> rnhuchwrote:
>
> > My platform is SQL Server 2005. One of the tables of my db has
> > 28589928 rows and one of the fields is real. When I backup and restore
> > this database to another server (using the SQL Server internal tool)
> > and do a SELECT SUM(<<field name>>) from <<table name>>, the results
> > from these two servers are slightly different. I don't understand the
> > reason because these databases are static and they should be exactly
> > the same.
>
> > Then when I tried to run the above select on the same server a few
> > times in a row, I also had inconsistent results (and no user is
> > accessing the database). This doesn't make any sense to me...

Thanks Gert-Jan and Plamen for your feedback.

On Sep 29, 4:33pm, rnhuch <rnh...@gmail.com> wrote:
> On Sep 26, 12:19pm, Gert-Jan Strik <so...@toomuchspamalready.nl>
> wrote:
>
>
>
> > In addition to Plamen's reply (or maybe it is also stated in the linked
> > pages), the exact value of approximate data types such as real depend on
> > the specific CPU of the specific server. If the two servers do not have
> > the exact same CPU(s), then you can get differences, even without
> > SUMming them.
>
> > Also note, that if you SUM both small and large numbers, then you could
> > loose precision. If you do, then the result can differ depending on the
> > order in which the numbers are added up, which (theoretically) can be
> > different each time you run the query.
>
> > --
> > Gert-Jan
> > SQL Server MVP
>
> > rnhuchwrote:
>
> > > My platform is SQL Server 2005. One of the tables of my db has
> > > 28589928 rows and one of the fields is real. When I backup and restore
> > > this database to another server (using the SQL Server internal tool)
> > > and do a SELECT SUM(<<field name>>) from <<table name>>, the results
> > > from these two servers are slightly different. I don't understand the
> > > reason because these databases are static and they should be exactly
> > > the same.
>
> > > Then when I tried to run the above select on the same server a few
> > > times in a row, I also had inconsistent results (and no user is
> > > accessing the database). This doesn't make any sense to me...
>
> Thanks Gert-Jan and Plamen for your feedback.

A possible reason is that the ORDER of the additions is different on
the two servers. Check out a good book on numerical computing, and
you'll see that if you are adding real numbers whose value differs in
orders of magnitude, it matters in what order you do the additions
(i.e., add larger numbers before smaller--or vice versa--I don't
remember which is recommended).

So one thing you might do to test this theory is made a view that
sorts the numbers largest to smallest, and then query that view to do
the sum and see if it is the same on both servers.