Exam 70-767 All Questions

View all questions & answers for the 70-767 exam

Exam 70-767 topic 1 question 70 discussion

Actual exam question from Microsoft's 70-767

Question #: 70
Topic #: 1

DRAG DROP -

Table1 -

Table1 -
You are designing an indexing strategy for a data warehouse. The data warehouse contains a table named
. Data is bulk inserted into
.
You plan to create the indexes configured as shown in the following table.

Which type of index should you use to minimize the query times of each index? To answer, drag the appropriate index types to the correct indexes. Each index type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Select and Place:

Show Suggested Answer

Suggested Answer:

Box 1: Clustered columnstore -
A clustered columnstore index is the physical storage for the entire table.
With columnstore index, SQL Server processes aggregate in BatchMode thereby delivering order of magnitude better performance when compared to rowstore.
SQL Server 2016 takes the aggregate performance to the next level by pushing aggregate computations to the SCAN node.

Box 2: Nonclustered columnstore -
A nonclustered columnstore index and a clustered columnstore index function the same. The difference is that a nonclustered index is a secondary index that's created on a rowstore table, but a clustered columnstore index is the primary storage for the entire table.
The nonclustered index contains a copy of part or all of the rows and columns in the underlying table. The index is defined as one or more columns of the table and has an optional condition that filters the rows.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview

by Nelly100 at April 28, 2020, 11:15 a.m.

Comments

Submit Cancel

ialberto

Highly Voted 5 years, 1 month ago

Hi, in my opinion the correct answers are Clustered Columnstore and nonClustered index

upvoted 22 times

eceb

4 years, 11 months ago

could anyone explain why the second index should be nonclustered index instead of nonclustered columnstore index? thanks

upvoted 1 times

eceb

4 years, 11 months ago

How do I choose between a rowstore index and a columnstore index? Rowstore indexes perform best on queries that seek into the data, when searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads because they tend to require mostly table seeks instead of table scans. Columnstore indexes give high performance gains for analytic queries that scan large amounts of data, especially on large tables. Use columnstore indexes on data warehousing and analytics workloads, especially on fact tables, because they tend to require full table scans rather than table seeks. https://docs.microsoft.com/es-es/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15

upvoted 1 times

...

Anette

4 years, 11 months ago

And why not Clustered Columnstore and nonClustered Columnstore?

upvoted 1 times

...

DudeHere

Most Recent 4 years, 8 months ago

Answer is Clustered Columnstore Nonclustered If you try to create another columnstore index you get: Msg 35339, Level 16, State 1, Line 3 Multiple columnstore indexes are not supported. https://blog.sqlauthority.com/2016/01/22/sql-server-2016-creating-additional-indexes-with-clustered-columnstore-indexes/

upvoted 4 times

...

Slava_bcd81

4 years, 10 months ago

hash index is even better for point lookups then Btree, so if Table1 is a memory-optimized (the question doesn't say yes or no) it's better to use hash index rather then Btree non-clustered

upvoted 1 times

Slava_bcd81

4 years, 10 months ago

Btree index performs the best for a range selections

upvoted 1 times

...

Cococo

4 years, 10 months ago

Can have only one columnstore index on table. Index 1 - Clustered columnstore because 'contains all the data and there are aggregates' Index 2 - Non Clustered index because of 'point lookups in Table1' - https://www.sqlshack.com/top-10-questions-answers-sql-server-indexes/

upvoted 1 times

...

ThomasKwakman

5 years, 2 months ago

Hi, Shouldn't it be the following answers? 1/ Non clustered columnstore; and 2/ Non clustered And by Non clustered, for me here it implicitly means Non clustered rowstore.

upvoted 1 times

...

Nelly100

5 years, 3 months ago

The first index shouldn't it be just a clustered Index? You can't put more than 2 columnstore indexes on one table. You can only add one columnstore index per table.) This option is typically used when most of the queries against a table return large aggregations, but another subset does a lookup by a specific value

upvoted 1 times

...