Using secondary id as foreign key to other table

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable.

• As you point out, you'll have a much simpler FK relationship.
• A small PK makes for small (and fast) indices. Your total table space will probably be made less by adding such a column.
• If business rules ever change, you won't have to reorder the table.

The only material downside that comes to mind is that you may lose performance on queries which benefited from clustering on the composite PK. If you think that's likely to be significant, than continue clustering on the composite candidate key, but put the PK on the synthetic key.

VARCHAR column as Primary Key is not a good choice as normally we create Cluster Index on same column.

Cluster Index on VARCHAR columns is a bad choice because of expected high fragmentation rate. Every new inserted key value will try to find its place somewhere between existing keys and normally cause page split and high index fragmentation. As a result poor performance and extra index rebuild/reorganise cost.

Secondly while using varchar key column as Foreign Key will take extra space as compared to Pseudo key auto-incremented column.

BUT

Clustered index on auto-incremented column may create "hot spot". Read this carefully Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Though hot spot could be a problem, when to many users are trying to insert values but still in your case i would like to go for auto-incremented column as compare to varchar.