Hi Ed,

Your calculation is correct. And you'll find the exact same calculation in the topics on estimating the size of clustered indexes and heaps (except that in those, ALL columns are counted, not just those that are in the index). This confirms my point that space usage of varchar is the same in tables and indexes.

Note that this formula gives the maximum length. The actual length will be the actual amount of characters plus 2 (*). For non-Unicode characters, it's safe to say that if the average actual length is less than the maximum length by 2 or more characters, you'll save space in both table and indexes by using varchar rather than char.

(*) For simplicity sake, I leave out the extra 2 bytes of overhead for a row that includes any varying length columns, since they are not per column but per row.

Please note that I only disagreed with one specific part of your post, that seemed to imply that varchar takes more space in an index than it does in the table. I explicitly added that I do agree with the rest!