Which four actions should you perform in sequence?

DRAG DROP

A table named Table1 has a clustered columnstore index. The table contains over 10 billion records. This represents 12 years of sales data. The table is stored in a single filegroup.

You drop the existing columnstore index. You allocate 13 new filegroups with files large enough to store the entire table.

You need to implement a new columnstore index that supports maximum data compression for data older than 2 years.

Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

View Answer

Answer:

Explanation:

Step 1: Create a nonclustered index by year

Step 2: Define a partitioning function A partition function must first be created in a CREATE PARTITION FUNCTION statement before creating a partition scheme.

Step 3: Define a partitioning scheme The CREATE PARTITION SCHEME command creates a scheme in the current database that maps the partitions of a partitioned table or index to filegroups. The number and domain of the partitions of a partitioned table or index are determined in a partition function.

Step 4: Create a partitioned clustered columnstore index. Use columnstore_archive data…

For partitioned tables and indexes, you can configure the compression option for each partition, and the various partitions of an object do not have to have the same compression setting.

For partitioned columnstore tables and columnstore indexes, you can configure the archival compression option for each partition, and the various partitions do not have to have the same archival compression setting.

The partition scheme must already exist in the database when you create a columnstore index.

References: https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data­compression?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact­sql?view=sql-server-2017