Yes, it is redundant.
On the other hand, it won't hurt anything (since the optimizer will ignore it and not add the data twice).
This is one of the many reasons not to take the suggested indexes at face value.
Nonclustered indexes always include a row locator.
For a heap this will be an 8 byte RID (File:Page:Slot). On a table with a clustered index it will be the clustered index key column(s). And it will always be the copied values not a pointer to the values. This duplication of CI key values into all non clustered indexes is why it is often recommended that the CI key be narrow and not frequently updated.
In the table shown in the question the Clustered index key is a 4 byte integer and potentially may also include a 4 byte uniqueifier for any duplicate key values.
In your case as the NCIs are not declared as unique the CI key will be appended to the NCI key.
For unique non clustered indexes the CI key would be added as included column(s) in the leaf pages unless explicitly made part of the key.
See Kalen Delaney: More About Nonclustered Index Keys for some additional information about how you can see this for yourself.
With these 4 rows of data all three indexes only consume a single 8KB data page.
SELECT index_id,
index_level,
page_count,
record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('people'), NULL, NULL, 'DETAILED')
Returns
+----------+-------------+------------+--------------+
| index_id | index_level | page_count | record_count |
+----------+-------------+------------+--------------+
| 1 | 0 | 1 | 4 |
| 2 | 0 | 1 | 4 |
| 3 | 0 | 1 | 4 |
+----------+-------------+------------+--------------+
The additional page shown in use by sys.allocation_units.total_pages is an IAM page. This is not used for storing data but just for tracking the pages and extents comprising the index.
Best Answer
It depends on the size of the values in the column you are adding. As David suggests the most accurate way of knowing is to create an index in a dev or test environment and see what effect it has there.
You can estimate though. If the added column is 8 bytes long (a
datetimecolumn for instance) and there are 100M rows, then you can expect it to add approximately 800,000,000 bytes to the index's leaf pages. If it is a variable width column then you need to estimate from likely data lengths, or if you can run a query against the production DB you can read it from real data usingSELECT SUM(DATALENGTH(ColumnBeingAddedToIndex)) FROM TheTable.This only accounts for the extra data added to the leaf pages in the index, but it should be accurate enough as an estimate as that will be by far the largest factor. There will be a little extra space taken by non-leaf pages too.
Also the above does not take compression into account, if that is enabled for your index. Compressed data can be much more difficult to model, so in that case you are back to testing by creating the index on realistic data as the only really accurate way to go.
Without knowing your table/index definitions it is not possible to give a more precise answer.