Creating and using your own datatypes in SQL-Server
If you are familiar with any programming language, you already know all about user-defined datatypes.
If not, here is a quick lesson.
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:
Following our example, you create the address user-defined datatype like this:
sp_addtype address, ‘varchar(30)’, ‘NOT NULL’
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.