What is a User-Defined Data Type?
A user-defined data type provides a convenient use of underlying native datatypes for columns known to have the same domain of possible values.
Example:
EXEC sp_addtype phone_number, 'VARCHAR(20)','NOT NULL'
CREATE TABLE Customer(cust_id smallint, cust_phone phone_number)
How to create User-Defined Data Types?
User-defined data types are based on the system data types in Microsoft SQL Server. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and NULLability. For example, a user-defined data type called postal_code could be created based on the char data type. User-defined data types are not supported in TABLE variables.
When a user-defined data type is created, you must supply these parameters:
- Name
- System data type upon which the new data type is based
- NULLability (whether the data type allows NULL values)
When NULLability is not explicitly defined, it will be assigned based on the ANSI NULL default setting for the database or connection.
Note: If a user-defined data type is created
in the model database, it exists in all new user-defined databases.
However, if the data type is created in a user-defined database, the
data type exists only in that user-defined database.
Examples:
The SQL Server user defined data types can be created both with SQL Server Management Studio and T-SQL commands. Let's walk through samples of each option to serve as an example of SQL Server user defined data types can be used with defaults and rules.
| 1.Creating SQL Server User Defined Data Types in SQL Server Management Studio (SSMS) | |
| > Open SQL Server Management Studio. > Navigate to the Databases | AdventureWorks | Programmability | Types folder. > Right click on the Types folder and select New | User-Defined Data Type | |
2.Syntax for creating SQL Server user defined data type | |
| sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @NULLtype = ] 'NULL_type' ] [ , [ @owner = ] 'owner_name' ] | |
Here is a basic explanation of the four parameters from the system stored procedure:
Parameter Explanation @typename Name of new user defined data type that is being created @phystype Base system data type of SQL Server @NULLtype To specify that NULL values are allowed for this data type or not @owner Owner of this being created user defined data type
Below is the example default and rule code used in SSMS above: Example
| Example Default and Rule Code 1.CREATE a default value for phone number to be used in example USE AdventureWorks GO CREATE DEFAULT Default_PhNo AS 'UnknownNumber' GO 2.CREATE a rule for phone number to be used in example Number will be in format +92-3335409953 or UnknownNumber by default USE AdventureWorks GO CREATE RULE rule_PhNo AS (@phone='UnknownNumber') OR (LEN(@phone)=14 AND SUBSTRING(@phone,1,1)= '+' AND SUBSTRING(@phone,4,1)= '-') GO |
In the final code snippet, we will bind the rules and defaults to the user defined data types:
Commands to bind the defaults and rules to user defined data types
To bind a default 'Default_PhNo' to user defined data type 'PhoneNumb'
EXEC sp_bindefault 'Default_PhNo', 'PhoneNumb'
To bind a rule 'rule_PhNo' to user defined data type 'PhoneNumb'
EXEC sp_bindrule 'rule_PhNo', 'PhoneNumb'
