Indexes

Question 1

Describe the make up of an index

Answer 1

• Pages (index nodes)
• B tree structure
• With a root, intermediate and leaf levels

Question 2

What are the 3 levels of an index

Answer 2

• Root level
• intermediate level
• leaf level

Question 3

How does the query engine find a row using an index?

Answer 3

• Starts at root and works down hierarchy until it reaches the leaf node and the value it is looking for.
• this node either contains the whole row or a pointer to the row in which case it will need to do an additional lookup.

Question 4

What is a clustered index

Answer 4

• An index where the whole row is stored with the indexed value in the leaf node
• Each table can have only one clustered index

Question 5

What is a non-clustered index

Answer 5

An index where a row locator to the row is stored with the indexed value in the leaf node

Question 6

What do you call an index which contains more than column?

Answer 6

Composite index

Question 7

What is an index with no repeated values called?

Answer 7

Unique index

Question 8

What type of index is created when a primary key is specified

Answer 8

By default it creates a unique clustered index on the primary key

Question 9

What do you call an index which has all the columns required to return a query

Answer 9

A Covering index

Question 10

When could you heavily index a table?

Answer 10

When the table is large and does not change much

Question 11

When should you minimize use of indexes?

Answer 11

• When the table has meany insert, updates and deletes

- Very small tables where a scan might be faster

Question 12

How does the uniqueness of an indexed column affect performance

Answer 12

-the more unique the values the better the performance

Question 13

Which columns in your queries are good choices for indexes

Answer 13

• Columns used in joins and predicates (e.g. WHERE)

- Columns in exact match queries

Question 14

What is a heap?

Answer 14

A table without a clustered index

Question 15

How can you add non-key columns to a non-clustered index

Answer 15

Using the INCLUDE( ) in the CREATE statement

Question 16

How does a clustered index and column store index differ in the way the table data is stored

Answer 16

The tables are stored in rows on the leaf nodes of a column store index
The each column in table is stored in its own segment and highly compressed

Question 17

Give 3 advantages of column store indexes over clustered indexes

Answer 17

• When a row store index is used even if only one column is being returned the whole row is loaded into memory rather than just one segment.
• Because each segment has the same data type it can be heavily indexed which means even more data can be brought into memory
• Batch processing mode can operate on batches rather than row by row

Question 18

What is a dictionary used for in a column store index

Answer 18

• It can be used with string columns or those with few distinct values to create a lookup.
• A reference is stored in column store rather than the value which saves even space