Exam 70-761 topic 1 question 183 discussion

Actual exam question from Microsoft's 70-761

Question #: 183
Topic #: 1

You have a date related query that would benefit from an indexed view.
You need to create the indexed view.
Which two Transact-SQL functions can you use? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

A. DATEADD
B. AT TIME ZONE
C. GETUTCDATE
D. DATEDIFF

Show Suggested Answer

Suggested Answer: CD 🗳️
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017#DateandTimeFunctions

by safiullah at Aug. 23, 2019, 12:56 p.m.

Comments

Submit Cancel

safiullah

Highly Voted 5 years, 10 months ago

Hi, I think the question here is related to deterministic versus non-deterministic functions. An index can not be created for a non-deterministic function. Therefore, the correct answer should be "DATEADD" and "DATEDIFF", because both of them are always deterministic functions as per below microsoft link. https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017

upvoted 15 times

M4x

5 years, 9 months ago

Agree. But the question is on VIEW, I try all 4 statements inside an INDEXED VIEW and ALL 4 working properly !

upvoted 2 times

...

Helgo

4 years, 8 months ago

A clustered index cannot be created on a view if the view references any nondeterministic functions.

upvoted 1 times

...

ivanbtod

Highly Voted 5 years, 5 months ago

The definition of an indexed view must be deterministic. DATEADD and DATEDIFF are deterministic

upvoted 12 times

...

Billybob0604

Most Recent 4 years, 5 months ago

Answer = A/D. Just tested this USE [70-461] GO if exists ( select * from INFORMATION_SCHEMA.VIEWS where [TABLE_NAME] = 'ViewByDepartment' and [TABLE_SCHEMA] = 'dbo' ) drop view dbo.ViewByDepartment GO CREATE view [dbo].[ViewByDepartment] with schemabinding as select D.Department , T.EmployeeNumber , T.DateOfTransaction , T.Amount as TotalAmount --,SYSDATETIMEOFFSET() at time zone 'Central European Standard Time' timezone_column2 -->nondeterministic --,getutcdate() as utc_date --> nondeterministic ,dateadd(day,1,t.dateoftransaction) as day_plus_1 -->deterministic ,datediff(hh, t.dateoftransaction,t.dateoftransaction) as diff_both_dates --> deterministic from dbo.tblDepartment as D inner join dbo.tblMedewerker as E on D.Department = E.Department inner join dbo.tblTransacties as T on E.EmployeeNumber = T.EmployeeNumber where T.EmployeeNumber between 100 and 300 GO

upvoted 1 times

...

mattia_88

5 years, 8 months ago

DATEADD e DATEDIFF are deterministic

upvoted 5 times

...