Replacement for deprecated SQL Server User Defined Type with a bound Rule and Default

Question

We have a User Defined Data Type of YesNo which has an which is an alias for char(1). The type has a bound Rule (must be Y or N) and a Default (N).

The aim of this is that when any of the development team create a new field of type YesNo the rule and default are automatically bound to the new column.

Rules and Defaults have been deprecated and won't be available in the next a future version of SQL Server, is there another way to achieve the same functionality?

I should add that I'm aware that I could use CHECK and DEFAULT constraints to replicate the functionality of the bound Rule and Defalut objects, however these would have to be applied at each usage of the type, rather than getting the functionality 'for free' by using a UDT which has a bound Rule and Default.

The post relates to a database that backs an existing application, rather than a new development, so I'm aware that our use of UDT's is less than optimal.

I suspect the answer to the question is 'No', however normally when features are deprecated there's usually an alternative syntax that can be used as a drop in replacement so I wanted to pose the question in-case someone knew of an alternative.

Answer 1

Default and check constraints...

CREATE TABLE foo (
   col1 int...
   YesNo char(1) NOT NULL DEFAULT ('N')
                   CONSTRAINT CK_foo_YesNo CHECK (YesNo IN 'Y', 'N'))
   col2 ...
   )

Personally, I tend not to use UDTs (last time was SQL Server 6.5 IIRC) because there is no ALTER TYPE in case anything changes...

As for deprecation..

First mentioned in CREATE RULE for SQL Server 2005. So, we were told 6 years and 3 releases ago

For SQL Server 2000...

"Rules, a backward compatibility feature, perform some of the same functions as check constraints. CHECK constraints, created using the CHECK keyword of ALTER or CREATE TABLE, are the preferred, standard way..."

The same applies to CREATE DEFAULT, the object not the constraint

That's 11 years ago