If you are familiar with any programming language, you already know all about user-defined datatypes.
If not, here is a quick lesson.
Datatype definition
A user-defined datatype is one that you define yourself to allow for easy storage of your own data. However, user-defined datatype is based on known existing MS SQL Server 2012 datatypes.
For example, suppose you wanted to create a user-defined datatype named “address”. Because the address datatype does not exist in MS SQL Server 2012 , you must create it using an existing MS SQL Server 2012 datatype, such as varchar(30). This allows for up to 30 characters of storage. You might even want to create the user-defined datatype to not allow null values.
You create a user-defined datatype with a system stored procedure (discussed in a later course in this series) called sp_addtype. The
sp_addtype system stored procedure uses this syntax:
sp_addtype type [,'system_data_type'] [,'null_type']
Type is the name that you will give your new user-defined datatype.
System_data_type is the existing MS SQL Server 2012 datatype that you will use in creating your user-defined datatype. This must appear in quotes.
Null_type is the NULL or NOT NULL keyword, if you choose to specify one. This must appear in quotes.
Because you have predefined the properties of the “address” datatype, you can then use the datatype as if it was an existing SQL Server datatype. An example of using the new user-defined datatype is shown in this code fragment:
In the next lesson, we will review the material covered in the module so far.