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' ]
It is the name of user defines data type. It must be unique in database.
It contains the MS SQL server data types.
It define that the user define data type may contain NULL value or not. It contains NULL, NOT NULL or NONNULL
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',
-- Using User define data type
CREATE TABLE emp_address
(Emp_name VARCHAR(50) NOT NULL,
I think this small posting is quite informative and thanking for giving time.
--Posted by: MR. JOYDEEP DAS