Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am trying to implement some constraints on tables so that there is no tampering of the data from what is expected.
One of the constraints is on a varchar column. It should at least contain one non-numeric character.
How can I implement this constraint in SQL Server, and if I implement it, What will be the effect on the performance if any?
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.
