How much database space is required during Index rebuild?

1. How much temporary space is required to create or rebuild a index ?
2. Whether it matters if you are creating/rebuilding a unique or a non-unique index ?
3. Whether it matters if you are creating/rebuilding an index on a partitioned or a non-partitioned table ?
4. Whether it matters if the index is a narrow single column index or a wide index that encompasses all the columns in the table  ?
5. Whether it matters if the index has included columns ?
6. What is the effect on the amount of temporary space and performance if you want the maximum concurrency while creating/rebuilding an index ?
7. What is the effect on performance and concurrency if you want to use the least amount of temporary space ?

Let’s us understand this via practical example

These are the Table specifications, where we wanted to create a index

• Size of the table = 1159633 pages = 8.85 GB
• Number of rows in the table = 179743073
• Size of each row = 42 bytes
• Number of columns in the index = 3 => 10 bytes

Here are the Results

1. Size of the clustered index created = 1159650 pages = 8.85 GB
2. Size of the mapping index in the TEMPDB = 603848 pages = 4.6 GB (approximately 52% of the index size)
3. Space used in TEMPDB for the sort runs = 1293728 pages = 9.88 GB (approximately 112% of the index size)
4. Total space used in TEMPDB = 1897576 pages = 14.48 GB (approximately 164% of the index size)