sp_addtype – Day 6 – SQL Server System Stored Procedure

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.

sp_addtype

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 

Follow me on Twitter

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

Leave a Reply

Your email address will not be published. Required fields are marked *