Exam 70-767 topic 1 question 124 discussion

Actual exam question from Microsoft's 70-767

Question #: 124
Topic #: 1

HOTSPOT -
You have a database that includes a table named dbo.Sales. The table contains two billion rows. You created the table by running the following Transact-SQL statement:

You run the following queries against the dbo.Sales. All the queries perform poorly.

The ETL process that populates the table uses bulk insert to load 10 million rows each day. The process currently takes six hours to load the records.
The value of the Refund column is equal to 1 for only 0.01 percent of the rows in the table. For all other rows, the value of the Refund column is equal to 0.
You need to maximize the performance of queries and the ETL process.
Which index type should you use for each query? To answer, select the appropriate index types in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer: Explanation
Query1: Nonclustered Index -
The query include a date range.
If you have included columns in your index, then the leaf level page of your non-clustered index contains the columns as defined in the nonclustered index the clustering key column(s) all those additional columns as defined in your INCLUDE statement.
Query2: Clustered columnstore index
Columnstore index is a new type of index introduced in SQL Server 2012. It is a column-based non-clustered index geared toward increasing query performance for workloads that involve large amounts of data, typically found in data warehouse fact tables.
Query3: Filtered nonclustered index
* When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.
When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table. Again, the resulting index will be smaller and cost less to maintain than a full- table nonclustered index.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes https://logicalread.com/sql-server-columnstore-index-w02/#.XRo06egzaUk

by petrutjim at Jan. 18, 2021, 7:10 p.m.

Comments

Submit Cancel

Simit

4 years, 5 months ago

We already have a clustered index because of the SalesID Primary Key. So how is it possible to have a clustered columnstore index for Query2?? I think it should be Non Clustered Columnstore index for Query2.

upvoted 2 times

...

petrutjim

4 years, 5 months ago

Query 1 - see explanation Query 2 - Columnstore - So to make SELECT COUNT(*) queries fast, here’s what to do ... and put a columnstore index on the table. Query 3 - Filtered nonclustered index - The benefit of using a filtered index is apparent in the scenario when you only select a subset of records from a huge table - Refund is only 0.01 percent

upvoted 1 times

...