0

I am currently working towards my certification in SQL Server 70-461. I'm working through some practice tests at the moment and have come across a question on requirements for an indexed view. I understand that indexed views must have SCHEMABINDING and COUNT_BIG(*) if a GROUP BY clause is used and that the index must be clustered and that this will then materialise the data.

CREATE VIEW VW_Test
AS
SELECT ColumnA, ColumnB FROM Table
WHERE ColumnC > 200

In the sample question, the index is to be created on ColumnA. ColumnB and ColumnC are both computed columns.

The question is, what are the requirements for ColumnB and ColumnC?

  • Determinstic
  • Precise
  • Marked PERSISTED

Unfortunately, in my training material I have not come across these terms in this context so if you can give me some guidance on what they mean then I will be able to figure it out from there.

Jace
  • 1,445
  • 9
  • 20
Beau6601
  • 51
  • 1
  • 3
  • 2
    VW test? Sounds like cheating... – jarlh Nov 16 '15 at 12:13
  • quite an unusual statement, did you read the question fully? I have put a sample of a quick view on here and I am asking a question regarding deterministic, precise and marked Persisted. I understand what deterministic means but want to understand it in terms of indexed views, why does it have to be deterministic etc. I'm not asking for anybody to answer the question as I would rather do that myself. – Beau6601 Nov 16 '15 at 12:59
  • It was a joke... Haven't you read the news the recent months regarding VW cheating in emissions tests? – jarlh Nov 16 '15 at 13:05
  • It kinda feels wrong to leave a question unanswered though, considering that it has high chances of coming up when comeone google those terms. So what's the answer? From what I understand, Column A has to be (Deterministic and Precise) or Persisted? – Julien Blanchard Nov 17 '15 at 14:11

1 Answers1

1

Deterministic: Refers to functions referenced by computed columns. Deterministic functions always return the same value when given the same input. For example, SUM is deterministic, but GETDATE is not.

Precise: A deterministic expression that does not contain float expressions.

Marked PERSISTED: When building a computed column, there is an option to mark it as 'PERSISTED' so that the computed column is physically stored to the database, rather than re-calculated on-the-fly when referenced.

As to the question itself about requirements for columns B and C, it would seem that that the following applies:

"Only precise deterministic expressions can participate in key columns and in WHERE or GROUP BY clauses of indexed views." Create Indexed Views

Jace
  • 1,445
  • 9
  • 20
  • As a runner up question, why does it have to be precise to be part of a key? – Julien Blanchard Nov 17 '15 at 14:11
  • In the article referenced in my answer it states that the constraint for a column being 'precise' if it is to be in the key is to 'ensure data integrity'. – Jace Nov 17 '15 at 14:40
  • my bad... that must've been the single paragraph I hadn't read. Heck, I even went on another page to get the answer and I couldn't find it. For further reference, a float's value depends on the processor that evaluates it, which means it's not a deterministic value (roughly). – Julien Blanchard Nov 17 '15 at 14:43
  • As for the solution, "A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic." This means that B doesn't need to be precise (but C needs to be because of what you quoted). – Julien Blanchard Nov 17 '15 at 14:51
  • I tried to not answer the exact question raised by the study guide since he said up above "I would rather do that myself" – Jace Nov 17 '15 at 14:53
  • Yeah, my main issue with that is how strongly weighted SO is on google, so someone searching for the answer might very well land on this page and not have the answer. – Julien Blanchard Nov 17 '15 at 14:58