2

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]);
')
Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
Jeff
  • 35,755
  • 15
  • 108
  • 220
  • 3
    you need to specify the SQL Server version....and your view definition. AND I shouldn't have to remind you that they are required (given the length of time you've used SO)... – Mitch Wheat Nov 26 '11 at 05:27
  • I'm happy to provide whatever information you need. If you're going to be snyde or condescending, go somewhere else to boost your ego as some sort of instructor. Your assistance is not appreciated. – Jeff Nov 26 '11 at 05:35
  • And with 100k+ reputation you would think it would obvious that commentary like "I shouldn't have to remind you" really isn't constructive. – Jeff Nov 26 '11 at 05:42
  • 1
    sure it is. you updated the question didn't you? Oh, the table schema might also be a good idea. – Mitch Wheat Nov 26 '11 at 05:43
  • Commentary like "you need to specify the SQL Server version....and your view definition" is constructive. That's why I updated my question. Commentary like "AND I shouldn't have to remind you that they are required" is only condescending. I thought I made the distinction clear, but I guess not. – Jeff Nov 26 '11 at 05:47
  • The whole schema for the involved tables is a bit large. If you really think it will help and is necessary I'll post it. I'd rather not clutter the question unnecessarily. The Amount column is a real data type...which seems to be the issue here. – Jeff Nov 26 '11 at 05:52
  • 2
    @JeffN825 When you paste code, please format it, and do not use tabs. It makes it harder to manage if changes need to be made – Adam Wenger Nov 26 '11 at 06:28
  • 1
    @JeffN825 You can take a look at http://www.poorsql.com for quick and easy formatting, I'm sure there are others too, but this one helps me. – Adam Wenger Nov 26 '11 at 06:35

1 Answers1

7

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.

rsbarro
  • 27,021
  • 9
  • 71
  • 75
  • 4
    In addition: using the real data type for accounting is gross neglect. Can end up with the software miscalculating unexpectedly. ALWAYS us precise data types and round numbers accordingly. – TomTom Nov 26 '11 at 06:26