question

Ontsnapt-6791 avatar image
0 Votes"
Ontsnapt-6791 asked Cathyji-msft edited

User defined data types

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Ontsnapt-6791 ,

Any update for this thread? Did the replies could help you?

0 Votes 0 ·
TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered ErlandSommarskog commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In addition to Tibor's post: if you use these rules and defaults, you cannot use the MERGE statement.

I worked with a system for many years, and we used this feature. Partly due to that this system started it's life in 4.x, but also because I liked the feature. However, I'm not working much with that system these days, and they have since then abandoned rules and defaults.

0 Votes 0 ·

Our database contains about 350 tables.

We use Powerdesigner for database changes. All domains are in Powerdesigner (data modelling tool). If a developer adds a column and chooses the domain we are sure the rule and default are in.

How is database design with SQL server done if you want to prevent that a developer forgets to add a rule or default.

0 Votes 0 ·

You will have to remember to add the appropriate CHECK and DEFAULT constraints for each column of that type. And, no, that is not a fantastic solution.

Although, it is possible that you can get PowerDesigner to generate those constraints for you when you ask it to generate scripts. We also used PowerDesigner for the above-mentioned system, but since we actually used rules and defaults, I don't know if were able to implement them as constraints. And it was a while since I last opened PowerDesigner.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered TiborKaraszi commented

Hi @Ontsnapt-6791,

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

But what the blog post doesn't mention is that the CREATE RULE and CREATE DEFAULT commands are deprecated. I.e., Microsoft actively states that we should avoid these command since they will be removed in a future version of SQL Server (according to MS documentation). See for instance https://docs.microsoft.com/en-us/sql/t-sql/statements/create-default-transact-sql?view=sql-server-ver15

0 Votes 0 ·