How to add constraint on a varchar column to have at least one non numeric character
A check constraint with a LIKE expression could be used to ensure at least one non-numeric character exists:

CHECK (YourColumn LIKE '%[^0-9]%')


The 0-9 range there might not be exactly right for you, depending on the collation of the varchar column. For example, using the common Latin1_General_CI_AS collation, the following additional characters would be considered numeric by the 0-9 test:

²
³
¹
¼
½
¾


If that is a concern, expand the range explicitly:

CHECK (YourColumn LIKE '%[^0123456789]%')


I would expect negligible performance impact in either case.