ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-764 All Questions

View all questions & answers for the 70-764 exam
Go to Exam

Exam 70-764 topic 1 question 147 discussion

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

DRAG DROP -
You have a server named SQL1 that has SQL Server 2012 installed. SQL1 hosts a database named Database1.
Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the Date field. The schema of Table1 is configured as shown in the following table.

Table1 contains the indexes shown in the following table.

You need to recommend an index strategy to maximize performance for the queries that consume the indexes available to Table1.
Which type of index storage should you recommend? To answer, drag the appropriate index storage type to the correct index in the answer area.
Select and Place:

Show Suggested Answer Hide Answer
Suggested Answer:
Index Storage Type -
Designing a partitioned index independently (unaligned) of the base table can be useful in the following cases:
- The base table has not been partitioned.
- The index key is unique and it does not contain the partitioning column of the table.
- You want the base table to participate in collocated joins with more tables using different join columns.
by vabjosh at May 4, 2019, 11:14 a.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
Iva10
4 years, 6 months ago
You have to maximize performance. For this non-aligned index is better https://mrfoxsql.wordpress.com/2015/05/13/deciding-whether-to-align-non-clustered-indexes/ For switchin partiton is better alligned index, and in this case is Date filed.
upvoted 1 times
...
MelKr
4 years, 6 months ago
I would say all indexes should be aligned. Non-Aligned indexes are only used in very special cases (unique, do not contain partitioning column). Brent Ozar even checks for non-aligned indexes as a potentially bad thing (https://www.brentozar.com/archive/2018/10/fixing-nonaligned-indexes-on-partitioned-tables/).
upvoted 1 times
...
DudeHere
4 years, 9 months ago
Date: Aligned All Others: NonAligned. To expand on BIG SWEDE (Thanks for all your input bro!): Aligned index: An index that is built on the same partition scheme as its corresponding table.
upvoted 2 times
...
TheSwedishGuy
4 years, 10 months ago
https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver15
upvoted 1 times
...
cian
5 years, 4 months ago
I think that Date should be aligned. The rest should be non aligned.
upvoted 2 times
...
vabjosh
5 years, 7 months ago
only date will be aligned and remaining will be non alligned
upvoted 2 times
...
vabjosh
5 years, 7 months ago
https://www.briefmenow.org/microsoft/which-type-of-index-storage-should-you-recommend-2/
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