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

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 th开发者_开发知识库ese 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.

---

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

---

"Rules and Defaults have been deprecated and won't be available in the next version of SQL Server"

1) As far as I know that is not true. You cannot suddenly break 99.9% of TSQL out there!

2) Also, even if it were true (and I strongly believe it is not), deprecated does not mean removed in the next release, simply that a feature should no longer be used in new code.

Do you have an official link to any such announcement?

User @gbn seems to think I am defending the use of deprecated constructs. I am not.

---

Xquery is a bit obtuse to use for something this simple. I typically use it for more complex data typing, but it does (I think) answer your question regarding a replacement. Here is how I might implement a parameter as typed xml which must be either true or false. You can extend it to be yes/no or puppy/kitten or whatever you'd like.

if schema_id(N'chamomile') is null
  execute (N'create schema chamomile');
go
set nocount on;
go
/*
  All content is licensed as [chamomile] (http://www.katherinelightsey.com/#!license/cjlz) and 
    copyright Katherine Elizabeth Lightsey, 1959-2014 (aka; my life), all rights reserved,
    and as open source under the GNU Affero GPL (http://www.gnu.org/licenses/agpl-3.0.html).
  ---------------------------------------------
*/
if exists
   (select xml_collection_id
    from   sys.xml_schema_collections as true_false
    where  true_false.name = 'true_false'
           and true_false.schema_id = schema_id(N'chamomile'))
  drop xml schema collection [chamomile].[true_false];
go
/*
  --
  -- License
  ----------------------------------------------------------------------
  Katherine E. Lightsey
  http://www.katherinelightsey.com

  All content is copyright Katherine Elizabeth Lightsey, 1959-2014 (aka; my life), all rights reserved, 
  licensed as [chamomile] (http://www.katherinelightsey.com/#!license/cjlz) and copyright Katherine Elizabeth Lightsey, 1959-2014 (aka; my life), all rights reserved, 
    and as open source under the GNU Affero GPL (http://www.gnu.org/licenses/agpl-3.0.html).

  --
  -- to view documentation
  -----------------------------------------------------------------------------------------------
  select objtype
       , objname
       , name
       , value
  from   fn_listextendedproperty (null
                  , 'schema'
                  , 'chamomile'
                  , 'xml schema collection'
                  , 'true_false'
                  , default
                  , default);
*/
create xml schema collection [chamomile].[true_false] as N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:chamomile="http://www.katherinelightsey.com/" targetNamespace="http://www.katherinelightsey.com/">

    <xsd:element name="true_false" type="chamomile:true_false_type" />

    <xsd:complexType name="true_false_type">
      <xsd:complexContent>
        <xsd:restriction base="xsd:anyType">
          <xsd:attribute name="true_false" type="chamomile:pass_fail_enumeration" default="false" />
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>

  <xsd:simpleType name="pass_fail_enumeration">
    <xsd:restriction base="xsd:NMTOKEN">
      <xsd:enumeration value="true" />
      <xsd:enumeration value="false" />
    </xsd:restriction>
  </xsd:simpleType>

</xsd:schema>';
go
declare @true_false xml([chamomile].[true_false]) = N'<chamomile:true_false xmlns:chamomile="http://www.katherinelightsey.com/" true_false="true" />';
if (select @true_false.value(N'(/*/@true_false)[1]', N'[sysname]'))
   = N'true'
  select N'true';
go
declare @true_false xml([chamomile].[true_false]) = N'<chamomile:true_false xmlns:chamomile="http://www.katherinelightsey.com/" true_false="false" />';
go
declare @true_false xml([chamomile].[true_false]) = N'<chamomile:true_false xmlns:chamomile="http://www.katherinelightsey.com/" true_false="not_valid" />';
go