[1] Implementing Indexes

Question 1

What is a Page in TSQL?

Answer 1

A page is a 8 KB unit and belongs to a single object (e.g. table).

Question 2

How many pages make an Extent?

Answer 2

8 (64KB).

Question 3

What are the two types of extents and how do they differ?

Answer 3

1. Mixed extent: when the underlying pages belong to multiple objects.
2. Uniform extent: when the underlying pages belong to only one object.

Question 4

How does SQL Server store pages in extents?

Answer 4

The first 8 in mixed extents and the subsequent in uniform extents.

Question 5

What are the two other names for a Heap?

Answer 5

Balanced Tree and Clustered Table.

Question 6

Is a Heap ordered in any way?

Answer 6

No.

Question 7

What is an IAM and what purpose does it serve?

Answer 7

Index Allocation Mapping organises pages/extents with objects.

Question 8

What is an IAM Page and what purpose does it serve?

Answer 8

An Index Allocation Mapping Page organises pages/extents with objects.

Question 9

How many IAM can an object have?

Answer 9

At least one.

Question 10

What is a Doubly Linked List and how does it work?

Answer 10

It is the structure SQL Server uses to organise large objects that have multiple IAM pages. Each page has a pointer to its decendent and antecendent.

Question 11

How does SQL Server store data in a Heap?

Answer 11

SQL Server stores new rows anywhere in a heap: i.e. in an existing page if there is space, or a new page/extent.

Question 12

What are the explicit and implicit ways a user can avoid data being stored in a Heap?

Answer 12

Explicit: creating a clustered index,
Implicit: primary key/ unique constraints.

Question 13

What is the name given to the situation in which not all the pages in a heap are full?

Answer 13

Internal Fragmentation

Question 14

How does Internal Fragmentation affect memory usage?

Answer 14

Because new rows are added to existing pages that are not full, SQL Server has less pages to read when retrieving information.

Question 15

Is a Clustered Index the same as a Balanced Tree?

Answer 15

Yes. It begins with a single IAM and multiple pages thereafter.

Question 16

What does SQL server do when a root page in a clustered index cannot point to all first leaf level pages?

Answer 16

It creates an intermediate level of pages which can themselves point to the first leaf level pages.

Question 17

What is a Uniquifier?

Answer 17

A Uniquifier is the name given to the process by which SQL server adds its own unique sequentially-ordered values to the repeating values of a composite key that contains columns that allows duplicates.

Question 18

What are the 3 ways a clustered index finds data?

Answer 18

1. logical order (when required),
2. physical order,
3. according to order of columns in clustering key

Question 19

What is fragmentation in indexing?

Answer 19

Fragmentation occurs when SQL adds rows to leafs that are not already full. It results in a splitting of the data from its physical ordering.

Question 20

What are the two types of fragmentation possible when SQL stores data?

Answer 20

Internal fragmentation occurs when there is still space available in a page for a new row. External fragmentation occurs when, forced to maintain a logical order, SQL server forces a row between two others in a page, forcing the page to split, changing the physical order of the records.

Question 21

What are the two clauses to combat fragmentation in indexing?

Answer 21

For Internal: WITH FILLFACTOR

For External: ALTER INDEX..REORGANIZE(REBUILD)