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 136 discussion

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

Note: This question is part of a series of questions that use the same or similar answer choices. As 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:

You frequently run the following queries:

There are no foreign key relationships between TBL1 and TBL2.
You need to minimize the amount of time required for the two queries to return records from the tables.
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 Hide Answer
Suggested Answer: B 🗳️
by raja1234567890 at June 3, 2020, 9:59 p.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
raja1234567890
Highly Voted 4 years, 11 months ago
Answer should be E by creating clustered columnstore index on Table1 which is greatly supported for huge table.
upvoted 6 times
Hoglet
4 years, 10 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/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15 From the description, TBL1 will hold 100 months of data, and we are querying 4 months worth or 4% of the table. That's a small range of values, and all columns. If we were querying selected columns for a larger date range then a columnstore index would be more useful.
upvoted 4 times
LeonLeon
4 years, 9 months ago
No it's not EEEEEEEEE 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. A nonclustered columnstore index enables real-time operational analytics where the OLTP workload uses the underlying clustered index while analytics run concurrently on the columnstore index. You simple can not create an Clustered and a non-clustered Columnstore index on the same table !!! https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15
upvoted 5 times
Luzix
4 years, 5 months ago
Beginning with SQL Server 2016 (13.x), you can have one or more nonclustered rowstore indexes on a columnstore index and perform efficient table seeks on the underlying columnstore.
upvoted 1 times
...
...
...
Anette
4 years, 10 months ago
I agree, I think E fits more here
upvoted 1 times
Chocho
4 years, 10 months ago
No because u can't create multiple columnstore in the same table
upvoted 9 times
stm22
4 years, 9 months ago
yes E because of the select * note: answer E has 3 sentences...i think the 2nd sentence is a typo.
upvoted 1 times
Aghie
4 years, 4 months ago
agree on this IF its really a typo otherwise its B
upvoted 2 times
...
...
...
...
MML
4 years, 10 months ago
" 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." It suggests to creaate two columnstore indexes in TBl1. We cannot have multiple columnstore indexes in a table.. Msg 35339, Level 16, State 1, Line 7 Multiple columnstore indexes are not supported.
upvoted 9 times
MML
4 years, 10 months ago
So I think B is correct
upvoted 3 times
LeonLeon
4 years, 9 months ago
B says: and include the most frequntly used column.. That can not be correct because there are two coumns in de selection of the query..
upvoted 1 times
...
...
...
...
KosteK
Highly Voted 4 years, 6 months ago
Agree. Answer B is correct. Tested. Create clustered index on column 1 in tbl1 -> gives index seek. Crete nonclusteredindex on column 2 with include column 5 and 6 in tbl2 ->gives also index seek
upvoted 5 times
...
Andy7622
Most Recent 4 years, 3 months ago
I would go with B. 1. Create clustered index on tb1- it selects all the columns so the it's selective. 2. Create nonclustered index- Only two columns are queried so we can include them in nonclustered index to benefit from that.
upvoted 1 times
...
eggzamtaker
4 years, 4 months ago
Answer is B.
upvoted 1 times
...
lh2607
4 years, 5 months ago
Only one columnstore index allowed per table. Its not E. Its B.
upvoted 1 times
...
Oooo
4 years, 7 months ago
if you ignore the second line of the E option ie create non clustered columnstore index on TBL1, then option E is correct. Because its redundant to have both clustered columnstore and non clustered columnstore on same table.
upvoted 1 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
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago