Exam 70-762 topic 1 question 148 discussion

Actual exam question from Microsoft's 70-762

Question #: 148
Topic #: 1

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in 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 have a Microsoft SQL Server database named DB1 that contains the following tables:

There are no foreign key relationships between TBL1 and TBL2.
You need to minimize the amount of time required for queries that use data from TB1 and TBL2 to return data.
What should you do?

A. Create clustered indexes on TBL1 and TBL2.
B. Create a clustered index on TBL1. Create a nonclustered index on TBL2 and add the most frequently queried column as included columns.
C. Create a nonclustered index on TBL2 only.
D. Create UNIQUE constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
E. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2.
F. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1.Make no changes to TBL2.
G. Create CHECK constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
H. Create an indexed view that combines columns from TBL1 and TBL2.

Show Suggested Answer

Suggested Answer: G 🗳️
A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.
Conditions for Creating Partitioned Views Include:
✑ The select list
✑ All columns in the member tables should be selected in the column list of the view definition.
The columns in the same ordinal position of each select list should be of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.
Also, at least one column (for example <col>) must appear in all the select lists in the same ordinal position. This <col> should be defined in a way that the member tables T1, ..., Tn have CHECK constraints C1, ..., Cn defined on <col>, respectively.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql

by Bartek at Feb. 16, 2020, 1:24 p.m.

Comments

Submit Cancel

vrab

4 years, 5 months ago

I think is C, A&B are not ok (When you create a primary key constraint, the database engine also creates a unique clustered index, if a clustered index doesn't already exist).

upvoted 1 times

...

stm22

4 years, 11 months ago

F tbl1 one has historical and aggregates, so columnstore. tbl2 already has pk so it has a clustered index which can find small num of rows

upvoted 4 times

Cococo

4 years, 9 months ago

ONE COLUMNSTORE INDEX PER TABLE.

upvoted 3 times

...

Vijayglobal

5 years, 1 month ago

How is it different from Q137 with an answer B ? I am lost here. Please Guide

upvoted 4 times

Mosufe

4 years, 10 months ago

In my point of view, it is not different and the answer is right.

upvoted 1 times

Cococo

4 years, 10 months ago

147 - You need to create SOMETHING (query) that includes data from both tables and minimizes the amount of time required for the query to return data. This one could be an indexed view (materialized) or Partitioned view, which is just a logical structure. Now, queries from table1 return 90% of aggregated data. Queries from table2 return lots of columns (select list is quite long) and 10% of all data. Because we need 'SOMETHING that includes data from both tables' we can choose from two options - Partitioned or Indexed view. Tables have no reference to each other, so we need to use UNION ALL, gues what? You cannot use UNION, INTERSECT or EXCEPT operators in the Indexed View, so we have only one option - Partitioned View (G) with the CHECK constraints

upvoted 2 times

...

Cococo

4 years, 10 months ago

148 - You need to minimize the amount of time required for queries(EXISTING AND DIFFERENT) that use data from TB1 and TBL2 to return data - this one is about INDEXES (D, G and H - we don't need them here) PK on the tables can be with Clustered or NonClustered index but there is a unique index 100%. + 1 condition to Table1 - it is Historical -> Clustered E and F telling us to create two Columnstore indexes on the same table, which is impossible (only if this question has a typo on this site) Now, we have A, B and C. I'd go with Clustered indexes for both tables, one Non-clustered index for the Table2 which is covering 90% of columns is useful for 10% of queries only. So. I vote for A.

upvoted 2 times

...

Bartek

5 years, 4 months ago

I am not sure. In my opinion the best answer here should include creation of columnstore indexes (big amount of data in tables). But answers with coulmnstore indexes are not satisfying me becouse we cant create Columnstore Clustered Index and Columnstore Non Clustered Index on this same table. Look at task order. As opposed to the previous task (147) here we dont have to return data from both tables by one query You need to create a query that includes data from both tables"... but we need te fire query separetly "You need to minimize the amount of time required for queries..." So in my opinion the best answer here will be A : "Create clustered indexes on TBL1 and TBL2."

upvoted 3 times

Nickname17

5 years, 2 months ago

If this is the case, then E satisfies you - E. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2. Because (1) becouse we can create Columnstore Clustered Index and Columnstore Non Clustered Index on this same table. (2) what on table 2 is nonclustered index rather than non clustered columnstore index.

upvoted 2 times

Mosufe

4 years, 10 months ago

Nickname17, this answers is a trap, they are saying to create a columnstore index 2 times in the same table and you just can have one "Drop existing indexes on *TBL1* and ***then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1***. Create a nonclustered index on TBL2."

upvoted 2 times

...