DRAG DROP
You have a server named SQL1 that has SQL Server 2014 installed. SQL1 hosts a
database named Database! Database1 contains a table named Table1. Table1 is partitioned
across five filegroups based on the TransactionType 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.
Answer: 
Explanation:
Note:
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.
Only ix_account nonaligned?
0
0
only ix_date – aligned
0
0
Table1 is partitioned
across five filegroups based on the (((TransactionType))) field
so
Ix type must be aligned and all the other indexes (not aligned)
https://msdn.microsoft.com/en-us/library/ms190787.aspx?f=255&MSPPError=-2147217396
0
0
+IX_Amount=align because “select SUM(Ammount) from T where TransactionType between 1 and 300” in partition index better.
0
0
@refaal: You are very right. Just like the original answers indicated, only IX_Type is an aligned index. TransactionType is part of the composite key because the table partition is based on the column. Any index that is in conformity with the underlying TABLE PARTITION, is an aligned index (PK_Table1 and IX_Type).
Every other index, be they clustered or nonclustered, as long as they do not reflect the underlying partition definition, they are non-aligned.
0
0
I think as the table is partitioned acording to transation type and the index for it is Ix-type thus the Ix type must be aligned and all the other indexes may not be aligned (not aligned)
0
0
Aligned
NotAligned
NotAligned
NotAligned
0
0
How about make all Index aligned regardless Index contains partition column or not?
https://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx
Although partitioned indexes can be implemented independently from their base tables, it generally makes sense to design a partitioned table and then create an index on the table. When you do this, SQL Server automatically partitions the index by using the same partition scheme and partitioning column as the table. As a result, the index is partitioned in essentially the same manner as the table. This makes the index aligned with the table.
0
0
probably make sense here
and
* 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.
none of indexes above falls under these conditions…
0
0