Index and Heap table storage on disk

Question

I have a few questions on tables and Indexed tables and its storage, Here is my first image where i have taken an example with Northwind database and Table name is Orders. Orders Table is with 9 indexes and Orders1 table has no indexes which i had replicated with statement(select * into orders1 from orders).

1. I had checked the space of table Orders and Orders1 where i found the index size is of Orders Table is more than data size and in Orders1 Only i can find Data Size is less.
2. Question 1:Is index size in orders table is cumulative of 9 indexes? or Its a single clustered index(as shown in below Fig.2)
3. Question 2:Each index partition shown below in Fig.2 has 830 rows is that mean to have duplicate page data on disk or memory?
4. Question 3:Does Non-Clustered index occupy space on disk or only Clustered Index?
5. Question 4:How does fill factor effect on storage of these clustered and non-clustered indexes.
6. How does index occupy size on disk(any references links/books/blog)?

Answers are welcome and greatly appreciated.

Best Regads,
Siva

Answer 1

First, some definitions:

• A clustered index is the data. When you see the size of a clustered index, that's the size of the table itself.

• A heap is a table that does not contain a clustered index.

• A non-clustered index contains the clustered index (or a row identifier if there is no clustered index), so that it can map back to the right row in the table, plus whatever column(s) you're indexing.

It's quite possible for the total size of non-clustered indexes to be larger than the table itself.

Let's answer your questions.

Is index size in orders table is cumulative of 9 indexes? or Its a single clustered index(as shown in below Fig.2)

This is the cumulative total of all non-clustered indexes (keeping in mind that each of them contains a copy of the clustered index).

Each index partition shown below in Fig.2 has 830 rows is that mean to have duplicate page data on disk or memory?

Maybe. SQL Server does not care if you create duplicate indexes, but just having the same number of rows does not make it a duplicate. Read more here about determining if an index is a duplicate.

The reason there are 830 rows in each index, is because the table has 830 rows, so each index contains the same number of rows, each of which maps back to the row in the clustered index (depending on which column(s) are indexed).

Does Non-Clustered index occupy space on disk or only Clustered Index?

All indexes consume space. A clustered index is the data in the table, and each non-clustered index is a copy of whatever data that it is indexing.

How does fill factor effect on storage of these clustered and non-clustered indexes.

This means that a percentage of space is left on each 8KB data page, to try reducing fragmentation. See link below about data pages.

How does index occupy size on disk(any references links/books/blog)?

The index is a data page like any other page in the database. It has a header, a slot array, and data in the middle that points to other data pages, and eventually gets to the leaf node where the data sits. You can read more about data pages here. Once you understand that, you can read about index pages here.