5

I'm creating my UDT:

CREATE TYPE [dbo].[Code]
FROM VARCHAR(20) NOT NULL

As RULES and DEFAULTS are considered deprecated, what is the recommended way to set constraint on my type to make sure that columns defined from this type contain only specific characters?

1
  • Do you know what needs to be done if you need to change the column width from 20 to, let's say, 30 characters?
    – A-K
    Aug 28, 2012 at 21:06

1 Answer 1

1

Currently, the only way to truly centralize/encapsulate this kind of logic (which is what I assume you're trying to do) is to use a CLR type. The good news it that this solution works with 2005+.

One alternative is to centralize the validation logic into a UDF, and then create individual check constraints that reference the UDF. If you're not using CLR already, this may be the best solution.

CREATE TYPE is very limited: when referencing a system type, it's just a type alias and nothing more. It would be nice to, for example, declare a check constraint (or something similar) that's associated with the type itself, but this is currently not implemented.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.