Quiz on partitioned tables and columnstore indexes

Grab a pen and notepad, and jot down your answers as you go, then check your answers at the key at the bottom of the page.

Questions

Q1. What should “Segment” really say in Statistics IO output?

1. Delta Store
2. Rowgroup
3. B+ Tree
4. Partition

Q2. Can you get both rowgroup elimination and partition elimination if you have a partitioned columnstore index?

1. If you have a partitioned columnstore index, you can’t get either
2. Yes, you can get both
3. No, if you get partition elimination you can’t get rowgroup elimination
4. No, if you get rowgroup elimination you can’t get partition elimination

Q3. When you see “Actual Partition Count” of 0 on a columnstore index, how should you interpret this?

1. No data access needed to be done
2. None of the rows were in the delta store
3. The table or index is empty
4. Partition elimination wasn’t done, but possibly rowgroup elimination was done

Q4. Why couldn’t we get partition elimination on the FirstNameId column in the demo in “More fun with rowgroup elimination and predicate pushdown”?

1. We were too busy giggling at the word “elimination”
2. The base table and nonclustered columnstore index were both partitioned on FakeBirthDateStamp
3. The column is an INT, and you can’t get partition elimination on INT data type columns
4. Our partitioned indexes were not “aligned”

Q5. What did it mean when we saw “Actual Number of Rows” was 0 on our Columnstore Index Scan for the query that was doing a COUNT(*) in the demo in “More fun with rowgroup elimination and predicate pushdown”?

1. The final count was zero rows
2. No rows needed to be counted by the hash match aggregate
3. No segments were able to be eliminated
4. Rowgroup elimination occurred even though partition elimination couldn’t occur

Q6. What is one issue with string data type columns in columnstore indexes as of SQL Server 2016?

1. You can’t use these columns as a partitioning key
2. You can’t have these columns in a columnstore index
3. You can’t get rowgroup elimination on these columns
4. There are too many cats playing with the string

Q7. As of SQL Server 2016 SP1, batch mode can only be used by SQL Server when your query…

1. … has a hint directing the query to use batch mode
2. … has a columnstore index operator in the query execution plan
3. … uses a linked server
4. … references a table with a columnstore index in the TSQL

Q8. How many values can be processed in a “batch” when batch mode is in use?

1. Up to 1 million, but it may be less
2. Exactly 900 in each batch
3. Up to 900, but it may be less
4. Exactly one million in each batch

Q9. What does Query Time Stats indicate on an operator in row mode?

• Values for just that node
• Cumulative values for the node and its children

Q10. What does Query Time Stats indicate on an operator in batch mode?

1. Values for just that node
2. Cumulative values for the node and its children

Scroll down for the answer key :point_down:

Answer Key

• A1. 2. Rowgroup
• A2. 2. Yes, you can get both
• A3. 4. Partition elimination wasn’t done, but possibly rowgroup elimination was done
• A4. 2. The base table and nonclustered columnstore index were both partitioned on FakeBirthDateStamp
• A5. 2. No rows needed to be counted by the hash match aggregate
• A6. 3. You can’t get rowgroup elimination on these columns
• A7. 4. … references a table with a columnstore index in the TSQL
• A8. 3. Up to 900, but it may be less
• A9. 2. Cumulative values for the node and its children
• A10. 1. Values for just that node