about multiplication in sql

what is the value when running sql script?

select cast(cast(.0000006 as numeric(38,10)) * cast(1.0 as numeric(38,10)) as numeric(38,10))

the result is .0000010000.

why the result is not .0000006000?

It looks to be rounding it upto the nearest decimal place, as the result has one fewer zeros to the right of the decimal point. WHy it would do this I am not sure.|||

The reason the scale of the result has been reduced to 6 is interesting. You have asked SQL Server to multiply two numerics, each with 38 precision and 10 scale.

Conceptually, the result should be a numeric(77, 20). However, this is above SQL Server's maximum precision, so the result would need to be truncated.

An implementation decision was made to sacrifice some scale to gain more digits on the left side of the decimal point, up to a minimum of scale 6. So the numeric(77,20) is actually converted to numeric(38, 6). The same behavior happens for division.

That decision is based off the assumption that digits on the left side of the decimal point are usually more important than the ones on the right side. It can pay off in a different situation:

select cast(1234567890123456789012345678.0 as numeric(38,10)) * cast(10.0 as numeric(38,10))

Here, the extra scale was not actually important, so sacrificing it to get more digits on the left side of the decimal point paid off.

To keep SQL Server from guessing if the scale is needed or not, you need to type your numerics appropriately if possible. In your example:

select cast(cast(.0000006 as numeric(10,10)) * cast(1.0 as numeric(2,1)) as numeric(38,10))

Yields the appropriate results. The result of the multiplication is conceptually numeric(13,11) which is well within our limits.

I hope this helps, and let me know if this work around does not work for you.

-Mat

|||In addition to Matt's comments, please take a look at the "Precision, Scale, and Length" topic in Books Online. The url is mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_da-db_8rc5.htm. This topic explains the formula used to arrive at the resulting precision of arithmetic operations involving two numeric values.|||thanks for your help~|||

Thank you for your reply.

I can find 'tsqlref.chm' but not know how to go '/ts_da-db_8rc5.htm' .Can you tell me how to do.Thanks.

---

I know how to find.^_^

use the tool bar,GO-->URL.... to open 'Jump to URL'

|||If you have SQL Server 2000 Books Online installed then go to Go\Url menu option and paste the url. You can also search for this topic in MSDN.