Performace and Sizes of Non-Clustered Indexes drops as size of clustering key increases?

Excerpt from: http://www.sqlservercentral.com/articles/Indexing/68563/

The width of the clustering key does not, however, only affect the clustered index. Th开发者_如何学运维e clustering key, being the rows’ address, is located in every single nonclustered index. Hence a wide clustering key increases the size of all nonclustered indexes, reducing their efficiency as well

Why should the size of the pointer to the datapage in the non-clustered index increase if the sizeof(int *) is the same as the sizeof(char *). Or is there some other addressing mechanism I am not aware of?

The pointer does not point to the data page: it points to the clustered index. Subtle, but different to where there is no clustered index where each NC index entry points to the Row ID (RID) in the data pages.

So if you have char(10) as your key, each NC index entry has a 10 byte pointer. If you have integer, then it's 4 bytes.

For char vs int, there are other considerations: collation at least (which determines case, accent, kana and width sensitivity).

And you string of 4 bytes only applies fot char(4). nchar(4) is 8 bytes, varchar(4) is 2 to 6 bytes (2 byte length), nvarchar(4) is 2 to 10 bytes.

In a clustered table, the value of the clustering key is a row pointer and hence implicitly appended to each record.

If (col1, col2, col3) is a clustered key, then an index on col4 is in fact an index on (col4, col1, col3, col3).

Its size of course depends on the sizes of col1, col2 and col3.