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:

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.

sp_addtype

Now let’s create a table used this user defined datatype ‘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 

Follow me on Twitter