Hi friends, today in SQL Server System Stored procedure series you will learn how to create user-defined datatype using stored procedure sp_addtype.
Sp_addtype is used to provide an alias to datatype. You can find the find the information of the newly created user-defined datatype in sys.types table for a specific database. To get enable user-defined datatype in all new user-defined databases you need to create that datatype in model database. User-defined data types cannot be defined for timestamp, table, xml, varchar(max), nvarchar(max), varbinary(max).
Syntax of sp_addtype is:
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] 'null_type' ] ;
Arguments
@typename is name of new user-defined datatype.
@phstype is system datatype of SQL Server
@nulltype is to specify whether NULL value is allowed for this datatype or not.
Let’s create an alias datatype for datetime datatype.
EXEC sp_addtype’Birthdate’,Datetime,’NOT NULL’
Here we have created Birthdate alias for Datetime datatype.
We can check our newly created alias in system table sys.types.
Now let’s create a table used this user defined datatype ‘Birthdate’
Create table Child
(
ChildName varchar(20),
DOB birthdate
)
So friends today we have learned how to create user-defined datatype. I will continue with other system stored procedures in my next blog.
Regards,
Kapil Singh
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
