varchar performance impact
First questions -


Yes they are stored on page normally (for non-(MAX) values)
They are not padded, but there is a 2 byte per row overhead to store the length of the field


1 - IT DEPENDS on if the page is full or not.  Very likely it will fit on the current page.  The header record for the row needs to updated as well to update the length of the field.

2 - Data will only be rearranged at the page level for an insert or update, so nothing else gets shifted.  If it fits on your current page it will, if not there will be a page split.

3 - Never as far as I know for varchar - if you want this behavior you can make it a CHAR instead.

4 - Only if the value exceeds the space on the page.

As to the design itself...

You will definitely get better performance by using the id instead of a varchar.


An int or smallint is orders of magnitude smaller than your strings.
Index lookups are a lot more efficient on integers than on strings
Data stored on the page will be consistent and much smaller
SQL likes keys!  A lot of optimizations can  take place when you use PK/FK relationships because the engine can infer some metadata about duplicates/distinct values, etc.