CODEWITHSUNDEEP
sqlsql-servert-sqlindexed-view
Beau6601
Beau6601

Reputation: 71

requirements for an Indexed View

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?

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.

Upvotes: 0

Views: 218

Answers (1)

Jace
Jace

Reputation: 1532

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

Upvotes: 1

Related Questions