sql-server - SQL Server 索引 View 列精度

Question

我正在尝试创建索引 View 并在创建索引时遇到以下错误:

Cannot create index on view ....' because column 'Amount' that is referenced by the view in the WHERE or GROUP BY clause is imprecise. Consider eliminating the column from the view, or altering the column to be precise.

有问题的列的数据类型为 real 我猜这是问题所在？

解决这个问题的合适方法是什么？我可以在 View SQL 中进行转换以消除“不精确”吗？

View SQL具体如下:

EXEC('
   CREATE VIEW model.ReceivableBillableParties
   WITH SCHEMABINDING
   AS
      SELECT pf.Id AS Id
         , pf.InsuranceId AS InsuranceId
         , pf.FinancialInsType AS InsuranceType
         , pr.ReceivableId
      FROM dbo.Receivables pr
      INNER JOIN dbo.Demographics pd ON pd.PersonId = pr.PersonId
      INNER JOIN dbo.Appointments ap ON ap.AppointmentId = pr.AppointmentId
      INNER JOIN dbo.Financiasl pf ON pf.PersonId = pf.PersonId
      INNER JOIN dbo.PracticeInsurers pri ON pri.InsurerId = pf.FinancialInsurerId
      WHERE pri.Amount = 0
')

EXEC('
   CREATE UNIQUE CLUSTERED INDEX [IX_ReceivableBillableParties]
   ON model.ReceivableBillableParties ([Id]);
')

Answer 1

documentation确实表明问题出在 real 数据类型上(请参阅精度要求)。如果您想在 View 的 WHERE 子句中使用该列，并为该 View 编制索引，则需要将该列更改为精确的数据类型(即，DECIMAL(9, 2)).

编辑
这documentation为存在此限制的原因提供了更清晰的解释。来自“确定性函数”部分:

Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture or version of microcode. To ensure data integrity, such expressions can participate only as non-key columns of indexed views. Deterministic expressions that do not contain float expressions are called precise. Only precise deterministic expressions can participate in key columns and in WHERE or GROUP BY clauses of indexed views.

希望对您有所帮助。