是否应该使用“ Sharepoint Number”列类型存储货币值？ [英] Should I use Sharepoint Number column types to store monetary values?

问题描述

在SharePoint中，我可以创建数字类型的列表列。我需要存储货币金额，并且希望能够使用此列类型，而不必创建一个新列。

In SharePoint I can create a list column of type 'Number'. I need to store monetary amounts and would like to be able to use this column type rather than having to create a new one.

此类型的值是否由存储和操作SharePoint（例如，在列表视图中对值求和时）以防止精度损失（即不是某种近似的浮点类型）？

Are values of this type stored and manipulated by SharePoint (e.g. when summing values in a list view) to prevent loss of precision (i.e. not as some kind of approximate floating point type)?

我看过货币列但是它们似乎会强制显示在我的应用程序中没有意义的货币单位（而且我怀疑它们以数字存储在阀盖下）。

I have looked at currency columns but they seem to force display of a currency unit which doesn't make sense in my application (and I have a suspicion that they are stored as 'Numbers' under the bonnet).

推荐答案

尽管不是确定性的，但基于以下测试，SharePoint似乎以适合货币计算的方式处理数字（至少使用WSS 3.0 / SQL Server 2005），即使它们大约存储。但是，具有超过15个有效数字的值可能会显示舍入误差：

Although not conclusive, based on the following tests it looks like SharePoint handles numbers in a way suitable for monetary calculations (at least with WSS 3.0/SQL Server 2005) even though they are stored approximately. However, values with over 15 significant figures can exhibit rounding errors:

测试资金存储和检索

三个数字列（第一，第二和第三）分别包含3.7、3.65和0.05（来自此处）和具有以下公式的计算列（返回一行文本）： = IF（First = Second + Third， Success ，失败）。在查看列表时，计算列显示成功。

Three number columns (First, Second and Third) containing 3.7, 3.65 and 0.05 respectively (.Net example from here) and a calculated column (returning a single line of text) with the following formula: =IF(First=Second+Third,"Success","Failure"). On viewing the list the calculated column displays Success.

测试货币计算

A是/否计算公式为 = 0.1 + 0.1 + 0.1 = 0.3 的列（.a示例来自此处）。在查看列表时，计算列显示是。

A Yes/No calculated column with the formula =0.1+0.1+0.1=0.3 (.Net example from here). On viewing the list the calculated column displays Yes.

测试货币存储量和计算I

在名为 TestList 的列表中，有一个自定义数字列（ CustomNumber ）包含304253.3251（来自 Microsoft白皮书）。这存储在数据库表 AllUserData 中，列 float1 的类型为 float （SQL Server 2005）。 float 是一种近似的数据类型。

In a list called TestList, a custom number column (CustomNumber) contains 304253.3251 (SQL Server example from Microsoft White Paper). This is stored in database table AllUserData, column float1 of type float (SQL Server 2005). float is an approximate data type.

运行以下查询：

DECLARE @ListName UNIQUEIDENTIFIER
SET @ListName = (SELECT tp_Id FROM AllLists WHERE tp_Title = 'TestList')

SELECT CAST(float1 as NUMERIC(18, 11)) AS CustomNumber 
FROM AllUserData 
WHERE tp_ListId = @ListName

SELECT float1 AS CustomNumber
FROM AllUserData 
WHERE tp_ListId = @ListName

给出以下结果：

CustomNumber
304253.32510000002

CustomNumber
304253.3251

使用公式 = CustomNumber * 100000000000 创建一个计算列，该公式可能会显示 30425332510000002的错误值实际上从用户的角度显示正确的值 30,425,332,510,000,000.00000 。我认为此行为是由于代码从数据库中读取了 float 值，并使用一个强制转换为数字适当的小数位数，并使用.Net小数类型处理内存中的值。

Creating a calculated column with the formula =CustomNumber*100000000000 which might be expected to display the incorrect value of 30425332510000002 actually displays the correct (from the user's perspective) value of 30,425,332,510,000,000.00000. I assume that this behaviour is due to the code reading the float value from the database doing a cast to numeric with a suitably small number of decimal places and manipulating the values in memory using the .Net Decimal type.

有建议，但是，在这种情况下，由于 float 的值已在两个版本之间更改。

There are suggestions, however, that on SQL Server 2000 calculation errors may manifest in this case since the behaviour of float values has been altered between the versions.

测试资金存储和取回II

基于上次测试的结果，将以下值添加到 CustomNumber 列：9999999999999999。

Based on the results from the previous test add the following value to the CustomNumber column: 9999999999999999.

此16位有效数字值在列表视图中（并编辑）错误地显示为10,000,000,000,000,000 （使用15位数字显示正确）。

This 16 significant digit value is displayed incorrectly in the list (and edit) views as 10,000,000,000,000,000 (using a value with 15 digits displays correctly).

ins观察 AllUserData 表显示该值错误地存储在数据库中并运行以下查询：

Inspecting the AllUserData table shows that the value is stored incorrectly in the database and running the following query:

DECLARE @f FLOAT
SET @f = 9999999999999999
SELECT CAST(@f as NUMERIC(20, 0))

给出结果：

10000000000000000

这表明SQL Server在插入时将数字四舍五入。

Which demonstrates that SQL Server is rounding the number on insertion.

这篇关于是否应该使用“ Sharepoint Number”列类型存储货币值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！