Exam 70-761 topic 1 question 94 discussion

Actual exam question from Microsoft's 70-761

Question #: 94
Topic #: 1

You have a disk-based table that contains 15 columns.
You query the table for the number of new rows created during the current day.
You need to create an index for the query. The solution must generate the smallest possible index.
Which type of index should you create?

A. clustered
B. filtered nonclustered with a getdate() predicate in the WHERE statement clause
C. hash
D. nonclustered with compression enabled

Show Suggested Answer

Suggested Answer: B 🗳️
A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.
Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes

by Anette at June 7, 2020, 11:38 p.m.

Comments

Submit Cancel

Angelcr

4 years, 5 months ago

B, the answer is correct.

upvoted 1 times

...

Vermonster

4 years, 5 months ago

D; Nonclustered with compression enabled due to non-deterministic function not being able to be used in the index filter predicate

upvoted 1 times

...

Andy7622

4 years, 6 months ago

GETDATE() won't work it gives current datetime we need a period of time (>= < OR BETWEEN AND)

upvoted 1 times

...

HA2020

4 years, 7 months ago

You can't use nondeterministic functions in the filter: https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-filtered-indexes/

upvoted 2 times

HA2020

4 years, 7 months ago

https://www.red-gate.com/simple-talk/sql/performance/introduction-to-sql-server-filtered-indexes/ From it: Using date functions also results in an error: --add nonclustered filtered index to ModifiedDate column CREATE NONCLUSTERED INDEX fIX_SalesOrderDetail_ModifiedDate ON AdventureWorks2012.Sales.SalesOrderDetail(ModifiedDate) WHERE ModifiedDate = GETDATE() GO 1794-5ace4e70-04df-4cfa-8036-4584c6e7294 Computed, spatial, and UDT columns are also not allowed as comparison criteria. The IN clause is allowed.

upvoted 2 times

BabyBee

4 years, 7 months ago

"You query the table for the number of new rows created during the *current day*". So guys, instead of using a date function to get today's date, we know what today's date is, so you need to put WHERE CreatedDate = '20201120' and it will work. CREATE NONCLUSTERED INDEX fIX_20110531 ON AdventureWorks2012.Sales.SalesOrderDetail(ModifiedDate) WHERE ModifiedDate = '2011-05-31 00:00:00.000' GO

upvoted 1 times

BabyBee

4 years, 7 months ago

Now I see the Answer B has specifically a "using the getdate() function".

upvoted 1 times

...

julie2020

4 years, 11 months ago

b or d ?

upvoted 1 times

...

Anette

5 years ago

I think it is D

upvoted 1 times

...