SQL Server 索引视图列精度

发布于 2024-12-18 06:18:00 字数 991 浏览 2 评论 0原文

我正在尝试创建索引视图并在创建索引时出现以下错误：

无法在视图上创建索引 ....' 因为列“金额”被视图引用 WHERE 或 GROUP BY 子句不精确。考虑消除该列从视图中，或者更精确地更改列。

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

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

视图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]);
')

原文

I'm trying to create an indexed view and get the following error creating the index:

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.

The column in question has a data type of real which I guess is the problem?

What's the appropriate way of resolving this? Can I do a convert in the view SQL to eliminate the "impreciseness"?

The view SQL is specified below:

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]);
')

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

千纸鹤 2024-12-25 06:18:00

文档确实指出了该问题取决于 real 数据类型（请参阅精度要求）。如果您想在视图的 WHERE 子句中使用该列，并为该视图建立索引，则需要将该列更改为精确的数据类型（即 DECIMAL(9, 2））。

编辑
此文档更清晰地解释了为何存在此限制。来自“确定性函数”部分：

即使表达式是确定性的，如果它包含浮点数
表达式，确切的结果可能取决于处理器架构
或微代码版本。为了确保数据完整性，此类表达式
只能作为索引视图的非键列参与。
不包含 float 表达式的确定性表达式是
称为精确。只有精确的确定性表达式才能参与
在关键列以及索引视图的 WHERE 或 GROUP BY 子句中。

希望有所帮助。