User defined data type concepts, syntax and an example.
User defined data types are based on system data types. It should be used when multiple tables need to store same type of data in a column and the user needs to ensure that all these columns have exactly same data type, length and nullability.
E.g.: Postal_code can be created based on char data type. One needs to provide Name, system data type, and nullability parameters while creating a user defined data type.
Syntaxsp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] 'null_type' ]
[ , [ @owner = ] 'owner_name' ]
User defined data type concepts, syntax and an example.
It extends base type datatypes functionality.
It is create when particular datatype format has to be used across many tables in the database.
If a column called EmpId appears in many tables of int type, create EmpId of int and use it across all your tables.
User defined data type concepts, syntax and an example.
User defined data types are most commonly used when consistency among several tables storing the same type of data is desired. User defined data types can also be used when several tables must store the same type of data in a column .Consistency in terms of same data type, length, and null ability is required. The user defined data type in SQL is based on system data type. When a user defined data type is created it must have a name, a system data type, whether or nor it can accept NULL values.
Syntax:Sp_addtype is used to create a user defined data type in transact SQL.
Example:To create a user defined data type for postal code.
Sp_addtype postal_Code, ‘varchar(10)’ , ‘NOT NULL’
