ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam
Go to Exam

Exam 70-762 topic 1 question 27 discussion

Actual exam question from Microsoft's 70-762
Question #: 27
Topic #: 1
[All 70-762 Questions]

Note: This question is part of a series of questions that use the same answer choices. An answer choice may be correct for more than one question on the series.
Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You work on an OLTP database that has no memory-optimized file group defined.
You have a table names tblTransaction that is persisted on disk and contains the information described in the following table:

Users report that the following query takes a long time to complete.

You need to create an index that:
- improves the query performance
- does not impact the existing index
- minimizes storage size of the table (inclusive of index pages).
What should you do?

  • A. Create aclustered index on the table.
  • B. Create a nonclustered index on the table.
  • C. Create a nonclustered filtered index on the table.
  • D. Create a clustered columnstore index on the table.
  • E. Create a nonclustered columnstore index on the table.
  • F. Create a hashindex on the table.
Show Suggested Answer Hide Answer
Suggested Answer: C 🗳️
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, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
by Heisenberg008 at Feb. 24, 2020, 3:26 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
Marlow
Highly Voted 5 years, 2 months ago
Wouldn't creating a clustered index men the existing index will be rebuilt? One of the requirements is that it does not impact existing index. For this reason, I think the answer should be B.
upvoted 13 times
Anette
5 years ago
No. For memory-optimized tables the index must be one of the following: Hash index Memory-optimized Nonclustered index Correct answer is A
upvoted 3 times
kiri2020
4 years, 7 months ago
it says -does not impact the existing index, so cannot be A - Existing nonclustered indexes on tables are rebuilt when a clustered index is created. https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15
upvoted 4 times
...
...
...
rya
Highly Voted 5 years, 3 months ago
the correct answer is A
upvoted 11 times
Vijayglobal
5 years, 1 month ago
IX_Value_type = Nonclustered Column stored index on the Value Type column. Also, it is an OLTP database. They have already provided a nonclustered column stored index. An only clustered index is left to complete the missing piece. I would like to refer Page 81 of 70-762 exam ref book.
upvoted 4 times
stm22
4 years, 11 months ago
i agree. A is correct. based on table info we were given, the table does not yet have a clustered index
upvoted 2 times
...
...
UsefJuan
4 years, 7 months ago
a non-clustered index is stored at one place and table data is stored in another place. which does not meet the third condition. correct answer is a clustered index A
upvoted 1 times
...
Hoglet
4 years, 6 months ago
A table does not require a clustered index. We only need the the COUNT of the number of rows.
upvoted 1 times
...
Andy7622
4 years, 5 months ago
How would a clustered index improve a performance in this case ? We will still have index scan on in this query . Index should be created on the selected column. Moreover we a looking for a specific date.
upvoted 1 times
...
...
caeesz
Most Recent 4 years, 5 months ago
there is DATE so C is correct
upvoted 1 times
...
Akang
4 years, 5 months ago
I just test, without clustered index, you can make normal nonclustered index. So I think that A is the correct answer
upvoted 1 times
...
CharlieBrownIsGreat
4 years, 7 months ago
The correct answer is likely C, because filtered indexes are using where clauses, and if the where clause matches the one used in the original question then the filtered index returns only 10,000 rows. 10,000 is a lot less than the 1 billion rows returned by a clustered index scan. 10,000 also addresses the performance issue, and the Microsoft documentation is almost verbatim in what filtered indexes achieve with the stated requirements. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes?view=sql-server-ver15
upvoted 2 times
...
lh2607
4 years, 7 months ago
Following up with my reasons for choosing B: "Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created." - One of the requirements is to not change the existing index, which a clustered index does, and the answer can therefore not be A.
upvoted 6 times
geekeek1
4 years, 5 months ago
exactly
upvoted 2 times
...
...
lh2607
4 years, 7 months ago
Can't be A since it would change the order, which is a requirement for it NOT to change. I'd go with B.
upvoted 2 times
...
ZSQL
4 years, 7 months ago
can't be NC filtered indexed. a filtered index spans a subset of Colum values only. filtered NC indexes are useful when some values in a column(null column) occur rarely to seek rare value. ref: 70-461 book
upvoted 1 times
...
kiri2020
4 years, 7 months ago
there seems to be misspell WHERE TransactionDate = (not -) and therefor F is the right answer- HASH index - as it performs excellent on equality filter options.
upvoted 1 times
kiri2020
4 years, 7 months ago
never mind, this is only right option for memory optimized tables, so the only option is left is B
upvoted 2 times
...
...
MML
4 years, 11 months ago
B. Create a nonclustered index on the table.
upvoted 10 times
ARehman
4 years, 6 months ago
B is right option. Filter is required on a specific day (not on a date range). and there are only 100,000 records on a specific day (as mentioned in the Notes Column (1st row)). Row store index will be fast enough to deal with 100,000 rows on a specific day. we can have both row store and column store indexes on table in SQL 2016.
upvoted 2 times
...
...
lukadataowner
5 years, 2 months ago
has no memory-optimized file group defined, the correct answer is B
upvoted 6 times
...
Postarion
5 years, 3 months ago
In my opinion decision is between A and B. I’d go with A because of requirement “minimizes storage size of the table (inclusive of index pages)” since clustered index will reorganize existing data without increasing storage size.
upvoted 1 times
Nickname17
5 years, 1 month ago
Really? I suppose the query requires only one coloum?
upvoted 2 times
...
...
Postarion
5 years, 3 months ago
Answers D and E are also not correct. It's because multiple columnstore indexes are not supported in SSMS.
upvoted 1 times
...
Postarion
5 years, 3 months ago
When using filtered index you can't use date functions such as DATEADD for a rolling date range – the value in WHERE clause must be exact. That's why the answer C is not correct.
upvoted 8 times
Jay2
5 years ago
You could drop and create one daily though. :-)
upvoted 2 times
...
...
JohnFan
5 years, 3 months ago
In SQL Server, rowstore refers to a table where the underlying data storage format is a heap, a clustered index, or a memory-optimized table. Beginning with SQL Server 2016 (13.x), you can create an updatable nonclustered columnstore index on a rowstore table. The columnstore index stores a copy of the selected columns, so you need extra space for this data, but the selected data is compressed on average 10 times. You can run analytics on the columnstore index and transactions on the rowstore index at the same time. The columnstore is updated when data changes in the rowstore table, so both indexes work against the same data. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15
upvoted 1 times
stm22
4 years, 11 months ago
i think "updatable nonclustered columnstore index" is a data warehouse index (fast retrieve,slowwrite). not for oltp
upvoted 3 times
...
NickMane
4 years, 9 months ago
irrelevant
upvoted 3 times
...
...
rya
5 years, 3 months ago
Value in where clause is not exact so can’t create filtered index based on where clause; C is out.
upvoted 4 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel