Exam 70-762 topic 1 question 137 discussion

Correct Answer is H

TBL1 and TBL2 are very similar, the only real difference being TBL2 has infrequent updates and TBL1 has none. Both are over 20million rows in size. Queries result hight percentage of rows, with aggregates. To me this says columnstore indexes, so options E or F. Neither provide a columstore index for TBL2, so the updates and deletes must be the issue. Might as well pick E as we can use the index to improve the performance of queries. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2016 If you don't agree with columnstore indexes, then B, purely because from the limited information, why not use an index with includes to improve performance. Not a great question

C is the least incomplete answer. So C.

Woops - sorry, I meant A using my reasoning. Also - a primary key does not need to be the clustered index. And since both these tables are unchanging or almost unchanging, there is little downside to changing an existing clustered index to something new (based on the aggregates) - note that in the study guide it is mentioned that the primary key is primarily important for table operations (INSERT, UPDATE, DELETE) which don't apply (so much) to these two tables.

In a world of imperfect options, I choose B as the best - assuming the options have no typos - which melvin9900 indicates. Creating a columnstore index for each table is the best option - if the question listed that as an option. But, unfortunately, because you can't add a second columnstore index to the same table, several of options are ruled out. We do know (see the answer reference link) that choosing a rowstore index has a positive affect if we know what index to choose - and because these two tables have aggregates, we would know what they are aggregating on. So I vote for B as the best of some suboptimal options and some clearly disallowed options.

Have a look at question 136-139, they seem like part of the same task: 136 - no link between tables - answer is B - clustered for both tables 137 - no link again - hell knows how many columns they have and what they are (plus first table has the historical data) - seems like A 138 - tables joined - aggregations - Answer is H - indexed view 139 - tables joined - PKs, 2016 and 2015 - answer is G - Partitioned view (check constraint needed) Looks like 137 with Column1 as PK is just teasing us to pick the Partitioned View but we have no idea about the columns of those tables:)

Answers is C A.B. clustered, But both tables have primary keys assigned to them. Therefore they already have a clustered index. E and F suggest multiple columnstore indexes in a table, that is not supported by SQL server.. D, G and H There are no foreign key relationships between TBL1 and TBL2. so you cannot create a meaningful result set to be return via a view

Queries return aggregates So i think should go with Column store. I believe the column store options are typo. From text book -105 "Columnstore indexes basically give you great aggregate and scan performance for most of the combinations of attributes you might consider without custom pre-planning."

I thin B is correct

D, G and H are wrong. There is no relationship between TBL1 and TBL2, so you cannot create a meaningful result set to be return via a view. What would the result set look like?

I think the answer is D because Table 1' contains historical data. Also, we'll need a UNIQUE constraint because there is chance that the primary key for both tables might be the same so the UNIQUE constraint will ensure there are no duplicates.

The correct answer is H