What is USER DEFINE Data Type and It’s Implementation

 

User define data type are based on system data type of MS SQL Server. It is created when several table object use the same data in terms of data type, length and null ability.

For example if we create the user define data type like, postal code. The postal code data type contains same length and non null character data type. The postal code data type can be used in several table objects where it is needed.

When you create the user define data types you must supply the parameters like.

1.    Name of the user define data type.

2.    System data type on which the user defines data type based on.

3.    Null ability, means whether the data type supports Null value or Not.

 

If we create the user define data types in model database, when we create the new data base on the same server the user define data type automatically exists in the new database.

The syntax of creating user define data types are mentioned bellow

sp_addtype [ @typename = ] type,

           [ @phystype = ] system_data_type

           [ , [ @nulltype = ] 'null_type' ]

           [ , [ @owner = ] 'owner_name' ]

 

@typename

It is the name of user defines data type. It must be unique in database.

@phystype

It contains the MS SQL server data types.

@nulltype

It define that the user define data type may contain NULL value or not. It contains NULL, NOT NULL or NONNULL

@owner

It specifies the owner or creator of the data type.

 

It returns the 0 for success or 1 for failure.

Example of creating user defines data types:

 

-- Creating User define data type

EXEC sp_addtype @typename='Postal_Code',

                @phystype='Varchar(7)',

                @nulltype='NOT NULL'

GO               

-- Using User define data type

CREATE TABLE emp_address

            (Emp_name    VARCHAR(50) NOT NULL,

             Postal_code Postal_code)

            

GO

 

 

I think this small posting is quite informative and thanking for giving time.

 

--Posted by: MR. JOYDEEP DAS