SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 04:59 PM
| ||||
| ||||
 Re: nolock float sum difference (sql srv 2k) A sum of many floats is sensitive to the order in which the summation is done. As a matter of fact, the exact same query might give different results on different runs depending on what is in the cache, what other activity is going on at the server, if the sum is split between several CPU:s, and so on. In short, accept that SUM(mycolumn) is inexact. If you want a sum that is always consistent, you could use SUM(CAST(mycolumn as numeric)) instead - that will always give you the same result, regardless of the summation order the server happened to use. /SG "Tim" <regtim@NOSPAM_shaw.ca> wrote in message news:cOIKa.322791$Vi5.8318215@news1.calgary.shaw.c a... > I just ran the following query both with and without the NOLOCK hint under > both sql server 7 and sql server 2000: > > SELECT SUM(mycolumn) FROM mytable (NOLOCK) > > The column is a float and there are about 800,000 records in the table. > > Under sql server 7 I get the same result with or without the nolock hint. > Under sql server 2000 (svc pk 3) I get different results with or without the > nolock hint. > > I have the same data in both machines (one machine has sql server 7 > installed, the other has 2000). > The result in sql server 2000 when I run without nolock matches the result > returned by sql server 7. So I'm assuming that using NOLOCK is giving me > inaccurate results. > > These are single-user development machines I'm running this on so this query > is the only activity - no other pending transactions or anything. > > Switching to compatibility level 70 did not change the results. > > Maybe I'm not phrasing it correctly, but I can't find it as a known bug. > > Has anyone else noticed this strange behaviour using aggregate functions on > floats with NOLOCK? > > Tim > >      |
Linear Mode
