how to create user defined Type in Sql

Question

i don't know how to create a domain with optional constrains? i tried

CREATE DOMAIN idx INT CHECK (VALUE > 100 AND VALUE < 999);

but faced the below err Unknown object type 'DOMAIN' used in a CREATE, DROP, or ALTER statement.

Solution 1

CREATE TYPE myType AS TABLE
(
   idx INT,
   CHECK (idx > 100 AND idx < 999)
)

Or you can also create rules and bind them to your type.

CREATE TYPE [dbo].[myType] 
FROM [INT] NOT NULL
GO

CREATE RULE multiplyByTen
AS @myType % 10 = 0
AND @myType > 100
AND @myType < 999

Then Bind Rule

EXEC sp_bindrule 'multiplyByTen', 'myType'

Solution 2

You might want to look at this.

It explains how to create a type in sql-server. There's 3 kind of types. The one you're trying to create does not allow adding a CHECK CONSTRAINT. You need to use rules instead.

In your case, you should be using this next queries:

--First we create the rule.
CREATE RULE range_rule  
AS   
    @range >= 100 AND @range < 999; 
GO

--Then we create the user-defined data type
CREATE TYPE int_and_range FROM INT;

--Then we bind the rule to the data type.
EXEC sys.sp_bindrule @rulename=N'[dbo].[range_rule]', @objname=N'[dbo].[int_and_range]'

After doing this, we could have such test:

CREATE TABLE test_table (
    custom_data_type_column int_and_range
)

--Try to insert a value against our rule
INSERT INTO dbo.test_table
        ( custom_data_type_column )
VALUES  ( 10  
          )

--RESULT:
--A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. 
--The conflict occurred in database 'db', table 'dbo.test_table', column 'custom_data_type_column'.  
--The statement has been terminated.

--Inserting a valid data:  
INSERT INTO dbo.test_table
        ( custom_data_type_column )
VALUES  ( 100 )

--RESULT:
--(1 row(s) affected)

--Select
Select * FROM test_table

--RESULT:

custom_data_type_column
-----------------------
100

(1 row(s) affected)

Beware though, CREATE RULE page says:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use check constraints instead. Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE. For more information, see Unique Constraints and Check Constraints.

Comments

[mistertodd]

sp_bindrule: Important This feature will be removed in a future version of Microsoft SQL Server.