User-Defined Types In SQL Server

When we are working with any programming language, we create user defined types as per our requirement. It is a better idea to have this feature in SQL server, right? Yes, we have the same kind of facility in SQL server -- we call it User-Defined Types.

We have mainly two types of User-defined types in SQL

User-Defined data type
User-Defined Tables Type

User Defined Data Types

When do we have to use it?

When there is a requirement to create a column with a datatype in the database and creating the same data type columns multiple times by multiple people. Here there is no guarantee that all developers will create the same type. In this situation we can make use of User-Defined data types. So, we create at one time and using the type wherever you need.

How to create User defined data types?

There are 2 ways to create it. The first one and the easiest method is from UI and the second one is using script.

Using UI steps to create User-defined Data types

Expand the data base where you want to create data types -> Programmability ->Types.

SQL server

Right click on the User-Defined Data Types and click on New User-Defined Data Type… . A window will pop up to create the type as shown in the below figure.

Enter the Name of the type in the Name textbox and select the base type from Data Type dropdown. Length input will be enabled based on the data type selection. And click on OK button of the popup.

Using Script to create User-defined Data types

I am trying to create 3 user defined data types for Name, Age and Location.

CREATE TYPE TypeName FROM VARCHAR(100)
CREATE TYPE TypeAge FROM INT
CREATE TYPE Location FROM VARCHAR(500)

Let’s use the above created Types for creating table or variable declaration.

CREATE TABLE EMP(EMP_Name TypeName, Emp_Age TypeAge,Emp_Location TypeLocation)
DECLARE @Name TypeName

User Defined Table Types

When we have to use it?

Generally, we declare tables to store temp data and use it in the next lines of the script

DECLARE @empTempTab Table (name varchar(100, age int, location VARCHAR(100))

Suppose we need to create the same kind of temp table in multiple SPs. In this case, instead of creating temp tables multiple times we can create required table type once and use it where ever we require.

How to create User defined Table types?

We don’t have UI to create the User-Defined Table as I explained in the Data types. We can create User-Defined Table types using script method.

Using Script to create User-defined Data types

Let’s take the above temp table structure.

CREATE TYPE empTempTabtype AS Table (name varchar(100), age int, location VARCHAR(100))

With the above statement we can create the table type.

Now let’s use the above table type to create the temp table.

DECLARE @empTempTab empTempTabType

Where we can find created types in the data base?

To find the created types in SQL server, go to database->Programmability->Types

There we can find following types

User-Defined Data Types
User-Defined Table Types

On expanding any of them, we can find our created types

SQL server

Limits of User defined types

User-defined types are accessible with in the Database only.
We cannot use Table types in the table valued functions

For Example

--Creating Table type
CREATE TYPE empTempTabtype AS Table (ID int identity(1,1),name varchar(100) default('ravi'), age int, location VARCHAR(100))
--Trying to create the Table valued function with the above type
CREATE FUNCTION dbo.ufnGetEMPInformation(@ID int)
RETURNS @retContactInformation empTempTabtype
AS
BEGIN
INSERT INTO @retContactInformation VALUES(@ID, 'ravi', 30, 'Hyderabad')
RETURN
END
--We will get an error on executing the above type.

We cannot alter the User-defined types after creation.

FAQs

Can we give default value for the table type columns?

Yes, we can give default values for the columns of the User-defined Table types. Check the following example.

--Creating Table type
CREATE TYPE empTempTabtype AS Table (ID int identity(1,1),name varchar(100) default('ravi'), age int, location VARCHAR(100))
--Creating temp table of above type
DECLARE @emp empTempTabType
INSERT INTO @emp(age, location) VALUES(23, 'Hyderabad'), (30, 'Hyderabad')
SELECT * FROM @emp
--Result will be as follows

SQL server

Can we add schema for types?

Yes, we can create schema wise types.

Can we create identity column for User-Defined Table types?

Yes, we can create the Identity column on the User-defined Table type. See the following example.

--Creating Table type
CREATE TYPE empTempTabtype AS Table (ID int identity(1,1),name varchar(100), age int, location VARCHAR(100))
--Creating temp table of above type
DECLARE @emp empTempTabType
INSERT INTO @emp VALUES('Ravi', 23, 'Hyderabad')
INSERT INTO @emp VALUES('Kira', 30, 'Hyderabad')
SELECT * FROM @emp

Result will be as follows,

SQL server
Can we use User-Defined Data types in the System function like CAST, CONVERT?

No , we cannot.

CREATE TYPE TypeName FROM VARCHAR(100)
select CAST(1 AS TypeName)

We get the following exception on executing the above script

Type TypeName is not a defined system type.