Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 147 discussion

Actual exam question from Microsoft's 70-762

Question #: 147
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 create a query that includes data from both tables and minimizes the amount of time required for the query 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 EdwardWang at Dec. 30, 2019, 9:25 p.m.

Comments

Submit Cancel

Froze

Highly Voted 5 years, 2 months ago

For TBL1 because of high percentage of rows and aggregate, clustered columnstore index will be ideal. If not, any other index will be fully scanned. For TBL2 nonclustered column-store index is OK. Nonclustered index is not, because high percentage of columns usage - you will need to include it to index, so columnstore is better. No looking at possible answers ... everyone of them are terrible. Less terrible seems to be F.

upvoted 6 times

Nickname17

5 years, 2 months ago

Only possible answers are C, D, G, H

upvoted 1 times

Nickname17

5 years, 2 months ago

Sorry I can’t delete it. Only D, G, H are possible.

upvoted 1 times

...

amar111

Highly Voted 5 years, 1 month ago

ITS - H . Indexed view

upvoted 6 times

Barbedx

4 years, 5 months ago

No, we can't use Union in indexed view, and we don't have any references between tables.

upvoted 2 times

...

BrianILyas

Most Recent 4 years, 6 months ago

I think H is correct indexed view. For partitioned view needs union all, there is no need of partitioned view.

upvoted 2 times

...

SoupDJ

4 years, 8 months ago

I think the answer is H. I understand all the comments made about trying to figure out whether the two tables need to be combined via a UNION or JOIN. I don't think the question provides enough information. However, the key observation (in my view) is that an indexed view can be built off of a partitioned view - as well if we're not talking about a partitioned view. The goal is make any resulting query that pulls from the two tables as fast as possible - an the use of an indexed view (which is, after all indexed and persisting) will be fastest.

upvoted 1 times

...

Alex5x

4 years, 8 months ago

Let's have a look at the question carefully and try to analyze the details. TBL1 - Contains 20 million records and most queries return a high percentage of rows from the table with aggregates. This immediatly tell us that we should have columnstore index on this table. This is because columnstore indexes great for working with large data sets, particularly for aggregation. Since Column1 is configured as the primary key, we can only add a non-clustered columnstore index.

upvoted 3 times

Alex5x

4 years, 8 months ago

TBL2 - most queries return a low percentage of rows. This means that seek operation will be the most performant here. Rowstore indexes best used for seeking a row, or a set of rows in order. So we should have a rowstore index here. Since Column1 has been configured as the primary key (PK), we already have a clustered index (by default). If the PK was created as nonclustered they had to write it clearly. So, we can only add a non-clustered rowstore index. Since most of the queries return a high percentage of columns we don't want to create covering index by adding a lot of columns to INCLUDE. If we add almost all columns from the table to INCLUDE we und up having a huge index almost duplicating the table. It is not a good idea for a table with 25 million records.

upvoted 3 times

Alex5x

4 years, 8 months ago

Now let's have a look at the options and try to exclude some of them: A. Create clustered indexes on TBL1 and TBL2. We already have clustered indexes on both tables, so we cannot add some more.

upvoted 1 times

Alex5x

4 years, 8 months ago

B. Create a clustered index on TBL1. Create a nonclustered index on TBL2 and add the most frequently queried column as included columns. We already have clustered indexes on TBL1. We want to create a nonclustered index on TBL2, but we don't want to add most frequently queried column as included columns.

upvoted 1 times

Alex5x

4 years, 8 months ago

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. First of all, we cannot drop the existing indexes on TBL1. Just try this: CREATE TABLE dbo.TBL1 ( COLUMN_1 int NOT NULL, COLUMN_2 varchar(128) NULL, CONSTRAINT PK_TBL1 PRIMARY KEY (COLUMN_1) ) GO DROP INDEX PK_TBL1 ON dbo.TBL1; You will get the following error message: An explicit DROP INDEX is not allowed on index 'dbo.TBL1.PK_TBL1'. It is being used for PRIMARY KEY constraint enforcement. So, the only way to drop the index is to drop the PRIMARY KEY constraint: ALTER TABLE dbo.TBL1 DROP CONSTRAINT PK_TBL1;

upvoted 1 times

Alex5x

4 years, 8 months ago

Let's assume we dropped the PRIMARY KEY and then created a clustered columnstore index. CREATE CLUSTERED COLUMNSTORE INDEX IX_1 ON dbo.TBL1; How to restore the PRIMARY KEY now? You cannot do it. The only way imitate it is to add a unique nonclusterd index: CREATE UNIQUE NONCLUSTERED INDEX IX_2 ON dbo.TBL1 (COLUMN_1);

upvoted 1 times

...

Load full discussion...

...

stm22

4 years, 11 months ago

It's not Check Constraint and Partitioned view because there is no specific column value to partition on. Earlier similar question had "TBL1 has records for 2016, TBL2 for 2015". So you could create check constraints like: ALTER TABLE TBL1 ADD CONSTRAINT CK_Year_2016 CHECK (YearColumn = 2016) ALTER TABLE TBL2 ADD CONSTRAINT CK_Year_2015 CHECK (YearColumn = 2015) then the partition view could use them. but this question does not have that in the description

upvoted 5 times

...

Hoglet

5 years ago

D. Create UNIQUE constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and 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. Regarding the View based answers, D, G and H. We can't join TBL1 and TBL2 in a view because there is no FK relationship. So that's H out. If TBL1 and TBL2 could be UNIONed togther, so not a FK relationship, but 2 tables of similar data (historical sales vs current sales), then we could have a partitioned view, in which case G is right. If you can't UNION the tables, and there is no suggestion in THIS question that you can, it's got to be one of the others. And none of them is good.

upvoted 6 times

...

Nelly100

5 years, 2 months ago

Stop misleading people if you dont know and dont bother to comment please. The correct answer here is Check Constraint and Partitioned view. SQL Server Partitioned Views enable you to logically split a huge amount of data that exist in large tables into smaller pieces of data ranges, based on specific column values, and store this data ranges in the participating tables. To achieve this, a CHECK constraint should be defined on the partitioning column to divide the data into data ranges. read more here: https://www.sqlshack.com/sql-server-partitioned-views/

upvoted 2 times

raja1234567890

5 years ago

Question is what column against what logic will be checked here?

upvoted 1 times

raja1234567890

5 years ago

On top of that, you have different set of columns being selected, so partitioned view is incorrect answer

upvoted 2 times

...

kimalto452

4 years, 6 months ago

Stop misleading people

upvoted 1 times

...

rya

5 years, 4 months ago

so is question 148 wrong then? this is the same question. they have different answers

upvoted 3 times

JohnFan

5 years, 3 months ago

An indexed view (sometimes referred to as a materialized view), is a view that has been made into more than just a simple stored query by creating a clustered index on it. By doing this, it basically makes it into a copy of data in a physical structure much like a table.

upvoted 1 times

...

EdwardWang

5 years, 6 months ago

B is correct one.

upvoted 3 times

JohnFan

5 years, 4 months ago

Why? Is this one different from Question #148 ?

upvoted 2 times

eggzamtaker

4 years, 5 months ago

Yes it is different.

upvoted 1 times

...

Bartek

5 years, 4 months ago

You just want to copy columns to Non Clustered index from 25 mln table ? i think Its bad idea. Think Answer G is correct, but I am wondering about H too

upvoted 4 times

JohnFan

5 years, 3 months ago

upvoted 2 times

MML

4 years, 11 months ago

So G seems to be the most appropriate

upvoted 1 times

...

stm22

4 years, 11 months ago

i think so too. Tbl1 does not contain historical data, as it does in other questions. So it is OLTP. thus columnstore might not be ideal.

upvoted 1 times

...