In this article, we will explore User-defined SQL Server Types and their usage.
An Overview of System Data Types
SQL Server supports various data types for storing different kinds of data. These data types store characters,
numeric, decimal, string, binary, CLR and Spatial data types. Once you connect to a database in SSMS, you can view
these data types by navigating to Programmability-> Types->System Data Types.
Here the data types are in different groups.
- Exact Numeric: bit, Tinyint, Smallint, Int, Bigint, Numeric, Decimal, SmallMoney, Money.
- Approximate Numeric: float, real
- Data and Time: DateTime, Smalldatatime, date, time, Datetimeoffset, Datetime2
- Character Strings: char, varchar, text
- Unicode Character strings: Nchar, Nvarchar, Ntext
- Binary strings: binary, Varbinary, image
- Other Data types: sql_variant, timestamp, Uniqueidentifier, XML
- CLR data types: hierarchyid
- Spatial data types: geometry, geography
You can explore my earlier article An Overview of SQL Server Datatypes for understanding these data types and their ranges.
User-defined SQL Server data types in SQL Server
If you expand the user-defined data types in the [AdventureWorks2019] database, you would see existing UDF’s in it.
As the name suggests, these data types are created by the user.
So, can we create our data types in SQL Server? Let’s explore these UDTs in this article.
User-defined SQL Server Types
The UDT is similar to an alias data type, and it uses the existing system data types in SQL Server or Azure SQL
database. SQL Server uses the Microsoft.Net framework. Common language runtime to define and implement the
Suppose your multiple tables use the email address column. The different developers might set a different value for
Nvarchar() data type. In this case, you can define a user-defined type, and you can use these types like a regular
SQL Server supports two kinds of user-defined types.
- User-defined data type
- User-defined table type
User-defined data types (UDDT) in SQL Server Types
A user-defined data type uses the existing data types with a set of constraints or rules. To create a UDDT,
right-click on the user-defined data type folder and New user-defined data type. On this page, you can see the
- Schema: By default, it uses the dbo schema. We can use any existing schema here
- Name: Give a customized name for the data type. Here, we give it a name as [PhoneNumber]
- Data type: Select the existing system data type to map with the user-defined data type. You can select the appropriate data type from the drop-down
- Length: Specify the length in case of a variable data type
- Allow Nulls: Put a check if you want to allow the NULL values
- Storage: It shows the bytes it consumes
Now, generate the script to be familiar with its equivalent T-SQL. It uses the CREATE TYPE statement, as shown
CREATE TYPE [dbo].[PhoneNumber] FROM [nvarchar](20) NOT NULL
Create the user-defined data type and refresh the object explorer. It shows you on the list, as shown below.
Now, you can use this user-defined data type similar to a regular data type.
CREATE TABLE [dbo].[SQLShackDemo](
[EmpID] [int] NULL,
[EmpName] [varchar](20) NULL,
) ON [PRIMARY]
In the [PhoneNumber] user-defined data type, we did not allow NULL values; therefore, if you try to insert a NULL,
it gives you the following error.
You can use this data type multiple times in any SQL tables, procedures, functions. For example, let’s create
another [CustomerInfo] table using the below script.
CREATE TABLE [dbo].[CustomerInfo] (
[CustID] [int] NULL,
[CustName] [varchar](20) NULL,
) ON [PRIMARY]
Now, suppose you got a requirement to increase the variable-length for data type used for both [EmpContactNumber]
and [CustContactNumber] columns.
In this case, we cannot alter an existing user-defined data type. We need to drop and recreate the user-define data
type, but it should have any reference objects.
You can follow two approaches.
- You can either remove object dependencies, recreate user-defined data type
- Alternatively, create a new user-defined data type and alter the table to use the new UDDT
CREATE TYPE [dbo].[PhoneNumberNew] FROM [nvarchar](50) NULL
Alter table [dbo].[CustomerInfo] alter column [CustContactNumber] [dbo].[phone]
Alter table [dbo].[SQLShackDemo] alter column [EmpContactNumber] [dbo].[phone]
Use binding for rules and defaults in user-defined SQL Server Types
We can bind the rules and default values in the user-defined data types as well. Suppose we want to have a contact
number with the following conditions:
- The phone number should have country codes
- We require the phone number format similar to +91- 1234567890
- Its length can be a maximum of 14 bytes
In the below query, we do the following things:
- If any user does not insert any contact number, it uses a default Unknown value
- It creates a rule to define the maximum length and symbols (+,-) at specific locations
CREATE DEFAULT PhoneNumber
CREATE RULE Rule_PhoneNumber
AND SUBSTRING(@phone,1,1)= ‘+’
AND SUBSTRING(@phone,4,1)= ‘-‘)
Now, create a new user-defined data type and map the existing default and rule, as shown below.
CREATE TYPE [dbo].[ContactNumber] FROM [varchar](20) NOT NULL
EXEC sys.sp_bindefault @defname=N‘[dbo].[PhoneNumber]’, @objname=N‘[dbo].[ContactNumber]’ , @futureonly=‘futureonly’
EXEC sys.sp_bindrule @rulename=N‘[dbo].[Rule_PhoneNumber]’, @objname=N‘[dbo].[ContactNumber]’ , @futureonly=‘futureonly’
Once we have created the new user-defined data type, create a test table and insert the sample records. It should
raise an error message if we try to insert any value that violates the rules definition.
Create table TestTable
Insert into TestTable values (‘Raj’,‘+91-1234567890’)
Insert into TestTable(UserName) values(‘Ram’)
Insert into TestTable values (‘Mohan’,‘5467890123’)
Insert into TestTable values (‘Hans’,‘123456789012345’)
Initial two insert statements work fine because it obeys the existing rule. The last two statements give an error
due to the violation of the rule.
In the table, we see two records. The first record is with the phone number, and the second record is with the default value.
User-defined table SQL Server Types
SQL Server allows a particular data type [table] to store a data set. In this data type, we specify the data types
and their properties similar to a table.
In the below user-defined table type, we defined two columns [ProductName] and [Cost] with their corresponding data
types. Table-valued parameters provide flexibility and better performance than the temporary table in some cases.SQL
Server does not maintain the statistics for these table-valued parameters; therefore, you should take you should
test your requirements and workload.
CREATE TYPE ProductTableType AS TABLE (
In the next step, we create a stored procedure that uses the user-defined table data type and selects the values from the variable.
It uses READONLY arguments for the table-valued parameters. We cannot perform the data manipulation operations –
Update, delete, insert on the table-valued parameters in the stored procedure body.
CREATE PROCEDURE GetProducts
@p ProductTableType READONLY
Now, inserts few records in the table variable and executes the stored procedure.
DECLARE @p as ProductTableType
VALUES (‘AC’, 123)
, (‘CA’, 345)
, (‘DB’, 543)
exec GetProducts @p
It returns the values as shown below:
Use default values in a user-defined table SQL Server Types
CREATE TYPE SalesOrderData
AS Table (
OrderID int identity(1,1),
CustomerName varchar(50) default(‘NA’),
DECLARE @s SalesOrderData
INSERT INTO @s(CustomerName, [location]) VALUES(‘Raj’, ‘India’),(‘Shyam’, ‘USA’)
SELECT * FROM @s
Creating a user-defined table SQL Server Types along with a primary key and clustered index
We can define the primary key, clustered and non-clustered index on a user-defined table type similar to a regular table.
In the below script, we define a table type [ProductTableType_1] and define the following things:
- It has [ProductID], [ProductName] and [Cost] columns
- It has a primary key on the [ProductID] column. By default, a primary key also creates a clustered index on the column
- It has a non-clustered index on the [Cost] column
CREATE TYPE ProductTableType_1 AS TABLE
[ProductID] int ,
INDEX NIX_COST (
Once we create a user-defined table type, refresh the object explorer and view the newly created table type.
Expand the user-defined table type [ProductTableType_1] and view the defined indexes (clustered, non-clustered) in
the indexes subfolder.
Right-click on an index to open its properties. It shows the index name, index type along with its column
In this article, we explored the user-defined SQL Server Types and the benefits of using them in your SQL Server
environment. You can explore these types and start using them, starting from the development environment.