An Overview of User-defined SQL Server Types

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 user-defined types.

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 data type.

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 following inputs.

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 below.

USE [AdventureWorks2019]

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,

[EmpContactNumber] [dbo].[PhoneNumber]

) 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,

[CustContactNumber] [dbo].[PhoneNumber]

) 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

AS 'Unknown'

USE AdventureWorks2019

CREATE RULE Rule_PhoneNumber

(@phone='Unknown')

OR (LEN(@phone)=14

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.

In the generated script from SSMS, we can see that it uses sys.sp_binddefault and sys.sp_bindrule stored procedures to bind these default and rules with user-defined data types.

USE [AdventureWorks2019]

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

(

UserName varchar(20),

PhoneNumber [dbo].[ContactNumber]

)

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 (

ProductName VARCHAR(50),

Cost INT);

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

SELECT ProductName,COST

FROM @P

RETURN 0

;

Now, inserts few records in the table variable and executes the stored procedure.

DECLARE @p as ProductTableType

INSERT @p

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'),

[location] VARCHAR(100)

)

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 ,

[ProductName] VARCHAR(50),

Cost INT,

PRIMARY KEY(

ProductID

INDEX NIX_COST (

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 properties.

Conclusion

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.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023