CODEWITHSUNDEEP
sql-serversql-server-2005indexingindexed-view
Jeff
Jeff

Reputation: 36573

SQL Server Indexed View Column Precision

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

Upvotes: 2

Views: 3885

Answers (1)

rsbarro
rsbarro

Reputation: 27339

The documentation does indicate that the problem lies with the real data type (see Precision Requirements). If you want to use that column in the WHERE clause of your view, and index that view, you'll need to alter the column to a precise data type (i.e., DECIMAL(9, 2)).

EDIT
This documentation provides a clearer explanation for why this restriction exists. From the section "Deterministic Functions":

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.

Hope that helps.

Upvotes: 8

Related Questions