User defined data types

Ontsnapt 21 Reputation points
2021-11-04T14:30:19.193+00:00

We want to migrate a SQL Anywhere database to SQL server 2019. In SQL anywhere we used a lot of domains.
In fact allmost all our columns in the tables refer to domains.

Example of a domain:
create domain CHECKBOX as numeric(1) default 0
check (@column is null or (@column between 0 and 1));

I suppose this domain is the same as a User Defined Data type in SQL Server.

Is it a normal procedure to use User Defined datatypes in SQL server?
What is the sql statement to create the "Domain" checkbox?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,639 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. tibor_karaszi@hotmail.com 4,301 Reputation points
    2021-11-04T19:30:00.71+00:00

    SQL Server don't have CREATE DOMAIN.

    We have something similar, but this is ancient, CREATE TYPE, CREATE RULE and CREATE DEFAULT, and then you can use sp_bindrule and sp_binddefault to the type.

    This was, sort of, "killed" in version 6.0 (1995) when we got ANSI SQL constraints. Since then, people prefer to use DEFAULT and CHECK constraints on a column/table instead of using above mechanism.

    The RULE and DEFAULT objects objects still exists, but you will feel very lonely of you use them.

    So, yes, there is a hole in the product regarding not having a good story on the domain concept, I'm afraid.

  2. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2021-11-05T06:56:25.013+00:00

    Hi @Ontsnapt ,

    I did not find useful information about Sybase SQL Anywhere CREATE DOMAIN Conversion to SQL server. Please read below blogs.

    Sybase SQL Anywhere CREATE DOMAIN Conversion to Other Databases
    SAP Sybase SQL Anywhere to SQL Server Migration

    If you want to find information about User-defined SQL Server Types. Suggest you reading the blog An Overview of User-defined SQL Server Types to get more details. It shows the detail examples.