Quiz: How index keys and included columns work

Q1. What does “FILLFACTOR=1” mean in this command?

  •  Fill the pages 1%, in the leaf and intermediate levels of the index
  •  Fill the pages 1%, only in the leaf of the index
  •  Fill the pages 99%, in the leaf and intermediate levels of the index
  •  Fill the pages 99%, only in the leaf of the index

[Show Answer] Fill the pages 1%, only in the leaf of the index. This command does not specify PAD_INDEX, so it will apply only to the leaf. For more on this, review the notes in “Create a test index and discover its structure.”

Q2. Fill in the blank

Hint: the word ends with a ‘d’

The built-in sp_helpindex command doesn’t show _________ columns for nonclustered indexes.

[Show Answer] included

Q3. Fill in the blank

Level 0 in an index is called the _______ of the index

[Show Answer] leaf

Q4. Which levels of an index contain non-key columns?

  •  Level 0 only
  •  Level 0 to Level 1
  •  Level 0 to Level 2
  •  All levels on the index

[Show Answer] Only level 0 includes non-key columns.

Q5. If an index has index_id 28, what does that mean?

  •  28 or more indexes of any type exist on the table
  •  It is the 28th index created on the table
  •  Nothing, the 28 is randomly chosen
  •  28 or more nonclustered indexes exist on the table

[Show Answer] It is the 28th index created on the table. This example is shown in the first video– we have only two indexes on the table, and the nonclustered index has index_id = 28.

Q6. Review the results from DBCC PAGE. To find rows with the last name ‘Little’, which child page would you seek to?

  •  158544
  •  158552
  •  158553
  •  158554

[Show Answer] 158553

Q7. Review the results from DBCC PAGE. What level of the index is this page from?

  •  The root page
  •  An intermediate page
  •  A leaf page
  •  This page is not in an index

[Show Answer] A leaf page

Q8. Seeks are good, scans are bad

  •  True
  •  False

[Show Answer] False. For more on this, check out the third question in the interview style video quiz.