Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
    
Do you know what needs to be done if you need to change the column width from 20 to, let's say, 30 characters? –  AlexKuznetsov Aug 28 '12 at 21:06
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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