Creating a User-Defined Type Using Visually or Programmatically in SQL Server 2012

Today, I have provided an article showing you how to create a user-defined type visually or programmatically in SQL Server 2012. User-Defined Data Types are special types defined by users using an existing base data type. If you have programmed in languages like C/C# then you are probably familiar with the ability to give a user-defined name to a known data type. Transact-SQL also gives you this option to create a user-defined data type. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

To visually create a user-defined type

Now Press F8 to open Object Browser in SQL Server Management Studio and expend it.

Database -> Programmability -> types-> Right click-> New-> User-Defined Data Types..

This would open the new user-defined data type window:

Now In the Name text box, enter a name of your choice. The name must follow the rules of names in Transact-SQL. In the Data Type combo box, select the data type of your choice. Of course, you must know what type you want to use and click OK Button. Now refresh the Object Browser to see the user-defined data type click on the user-defined data types under types folder.

How to Test new created data type

Create a new table to use the user-defined data type (zip).

Create Table TestUDD

(

      PinCode [zip]

)

go

INSERT INTO TestUDD  VALUES (10);                

INSERT INTO TestUDD  VALUES (22);             

INSERT INTO TestUDD  VALUES (323);

go

select * from TestUDD

Now press F5 to see the output:

Create a user-defined type programmatically

The system procedure sp_addtype is used to create a new user-defined data type programmatically.

sp_addtype Syntax

sp_addtype type_name data_type[,null_type]

Type_name: Type_name is the name of the new user-defined data_type that must be unique in the current database.

Data_type: Data_type is the Transact-SQL base data type, on which the user-defined datatype is based.

Null_type: Null_type specifies how the new user-defined data type handles null values (NULL or NOT NULL).

For example:

USE master

go       

sp_addtype zipcode,integer,null

go

In the preceding example zipcode is created which is a user-defined data type with the type integer.

Now Press F5 to execute the query.

After the execution of the procedure sp_addtype, refresh the Object Browser to see the user-defined data type click on the user-defined data types under the types folder. 

Database -> Programmability -> types-> User-Defined Data Type

How to Test new created data type

Create a new table to use user-defined data type (zipcode).

create table address

(

city varchar(20),

zipcode zip,

street char(27)

)

go

insert into address values('Delhi','281403','gh2')

insert into address values('Delhi','281403','gh2')

insert into address values('Agra','281434','bh3')

go

Select * from address

Now press F5 to see the output: