如何控制计算列的数据类型? [英] How do I control the datatype of a computed column?

问题描述

使用SQL Server 2012 ...

Using SQL Server 2012...

我有两列:

Price [decimal(28,12)]
OustandingShares [decimal(38,3)] -- The 38 is overkill but alas, not my call.

当我执行ALTER TABLE时，我得到的结果计算列为[decimal(38,6)].我需要数据类型为[decimal(28,12)].

When I do an ALTER TABLE I get a resulting computed column as a [decimal(38,6)]. I need the datatype to be [decimal(28,12)].

ALTER TABLE [xyz].MyTable
ADD Mv AS OustandingShares * Price

如何在此计算列上有效地获得12位小数位数?我尝试将OutstandingShares的转换转换为小数点后12位，以及将转换转换为OutstandingShares *价格.我唯一得到的是一个位于[decimal(28,12)]的计算字段，该字段具有六个尾随零.

How can I effectively get 12 decimals of scale on this computed column? I've tried doing convert on the OutstandingShares to 12 decimal places as well as wrapping a convert around the OutstandingShares * Price. The only thing I get is a computed field at [decimal(28,12)] with six trailing zeros.

有想法吗?

推荐答案

修复

这就是您想要的:

The Fix

This does what you want:

CONVERT(DECIMAL(28,12), (
                            CONVERT(DECIMAL(15, 3), [OustandingShares])
                          * CONVERT(DECIMAL(24, 12), [Price])
                         )
       )

对此进行测试:

SELECT CONVERT(DECIMAL(28,12),
                 (CONVERT(DECIMAL(24,12), 5304.987781883689)
               * CONVERT(DECIMAL(15,3), 3510.88)));

结果:

18625175.503659806036

18625175.503659806036

原因

由于SQL Server有关如何在各种操作中处理Precision和Scale的规则，计算被截断了.这些规则在MSDN页面中针对精度，小数位数和长度进行了详细说明.在这种情况下，我们感兴趣的详细信息是:

The Reason

The computation is being truncated due to SQL Server's rules for how to handle Precision and Scale across various operations. These rules are detailed in the MSDN page for Precision, Scale, and Length. The details we are interested in for this case are:

• 操作:e1 * e2
• 结果精度: p1 + p2 + 1
• 结果标度*: s1 + s2

• Operation: e1 * e2
• Result precision: p1 + p2 + 1
• Result scale *: s1 + s2

正在播放的数据类型为:

Here the datatypes in play are:

• DECIMAL(28，12)
• DECIMAL(38，3)

这应该导致:

• 精度=(28 + 38 + 1)= 67
• 比例= 15

但是DECIMAL类型的最大长度是38.那么，结果如何呢?现在，我们需要注意，结果量表"的计算中附加了一个脚注，即:

But the max length of the DECIMAL type is 38. So what gives? We now need to notice that there was a footnote attached to the "Result scale" calculation, being:

*结果精度和小数位的绝对最大值为38.当结果精度大于38时，将减小相应的小数位，以防止结果的整数部分被截断.

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

因此，为了使Precision降到38，它砍掉了小数点后9位.

So it seems that in order to get the Precision back down to 38 it chopped off 9 decimal places.

这就是为什么我建议的修复程序有效的原因.我将"Scale"值保持不变，因为我们不想截断，并且扩展它们没有用，因为SQL Server会适当地扩展Scale.关键在于降低精度，以使截断不存在或至少最小.

And this is why my proposed fix works. I kept the "Scale" values the same as we don't want to truncate going in and expanding them serves no purpose as SQL Server will expand the Scale as appropriate. The key is in reducing the Precision so that the truncation would be non-existent or at least minimal.

使用DECIMAL(15, 3)和DECIMAL(24, 12)我们应该得到:

• 精度=(15 + 24 + 1)= 40
• 比例= 15

40超出限制，因此减少2到38，这意味着将Scale减少2，使我们获得的真实"Result Scale"为13，这比我们需要的多1，甚至可以看到.

40 is over the limit so reduce by 2 to get down to 38, which means reduce the Scale by 2 leaving us with a true "Result Scale" of 13, which is 1 more than we need and will even be seeing.

这篇关于如何控制计算列的数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！