Explaining User-Defined Types in SQL Server?

Having recently stumbled on User-Defined Types (UDTs) in SQL Server. I thought I’d write a blog to explain what user-defined types are? Why you should you use them? This blog post delves into what UDTs are, why they are useful, and how to use them in SQL Server.

What are User-Defined Types (UDTs)?

In SQL Server, User-Defined Type (UDT) are a custom data type that you can create to extend the built-in data types. UDTs allow you to define your own data structures with specific properties and behaviours. They are created based on the system data types and can include multiple elements and methods. UDTs enable you to extend the database’s capabilities to store complex data and provide custom methods to manipulate that data.

Why Use UDTs?

1. Code Reusability and Encapsulation: UDTs promote code reusability. Once defined, they can be used across multiple tables and databases. Encapsulation allows hiding the implementation details from the user, providing a clear and simple interface.
2. Complex Data Handling: UDTs are particularly useful when dealing with complex data that doesn’t fit neatly into standard SQL data types. For example, you can create a UDT for a geographical location that includes latitude, longitude, and altitude.
3. Data Integrity: By encapsulating related data and behaviours, UDTs help maintain data integrity. You can define rules and behaviours that ensure data is always in a valid state.

Types of UDTs:

• Distinct Types: These provide a new name for an existing data type, allowing you to enforce specific constraints or rules.
• Structured Types: These define a composite structure made up of multiple fields or properties.
• Reference Types: These reference other UDTs or built-in types.

How to Use UDTs

UDTs can be used in various ways:

• In the definition of database objects (tables, views, etc.).
• As variables in Transact-SQL batches.
• In functions and stored procedures.
• As arguments in functions and stored procedures.

Script Examples

1. Finding UDTs

To find if any UDTs exist on the server the following script can be used.

SELECT * FROM sys.types
WHERE is_user_defined = 1

2. Create a Column UDT

This is a great feature for repeatability across different databases and tables. Once you’ve registered the assembly containing the UDT definition in the SQL Server database, you can use it in a column definition.

For example you want to define date types as smalldatetime and accepting NULLs. Then you can define the UDT name column in a column definition as if it were one of the intrinsic SQL Server data types.

CREATE TYPE StdDate
FROM smalldatetime NULL

This can then be used when you define table as you would use any other data type.

CREATE TABLE UDTExample 
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    StartDate StdDate
)

3. Create a Table UDT

This is an example of a User-Defined Type Table.

CREATE TYPE [dbo].[Address] AS TABLE(
    [id] [int] NULL,
    [fullName] [varchar](255) NULL,
    [address] [varchar](255) NULL,
    [address1] [varchar](255) NULL,
    [postcode] [varchar](10) NULL
)

This can be used to standardise data tables across a servers. An example would be the code below, where we can ensure the data parameters are set as per the type.

CREATE TYPE Countrylocation  AS TABLE
(
id INT identity(1,1),
Name varchar(200),
Country varchar(8)
)
GO
DECLARE  @c Countrylocation    
INSERT INTO @c(Name, Country) VALUES
('Daniel', 'India'),('Nina', 'USA') 
SELECT * FROM @c

4. Inserting into a Table Type

Anther common scenario is to use a table type and stored procedure to insert data. A script example is below. Note that the table is ReadOnly you cannot perform INSERT, UPDATE, DELETE, or MERGE operations

This in the required method when using a stored procedure in a Sink in Azure Data Factory (ADF) or indeed can be use to insert data in SSIS (SQL Server Integration Services).

CREATE TYPE [dbo].[DBS] AS TABLE(
    [fullName] [varchar](255) NULL,
    [id] [varchar](255) NULL,
    [customDBSRequirement] [varchar](255) NULL,
    [customStatus] [varchar](255) NULL
)
GO
CREATE OR ALTER PROCEDURE [dbo].[sp_InsertDBSRecord]
    @Tasks [dbo].[DBS] READONLY
AS
BEGIN
    SET NOCOUNT ON;
    -- Iterate over the records in the @Tasks table-valued parameter
    INSERT INTO DBS_Table 
    (
        FullName, 
        EEID, 
        DBS_Requirement, 
        DBS_Status
    )
    SELECT
        t.fullName, 
        t.id, 
        t.customDBSRequirement, 
        t.customStatus
    FROM @Tasks AS t;
END;
GO

Conclusion

User-Defined Types (UDTs) in SQL Server represent a powerful feature that allows developers to extend the database’s native capabilities. These allow the handling of complex and specialized data more efficiently. They offer the benefits of enhanced code reusability, encapsulation and precise data integrity. They give the ability to deal with complex data structures in a way that native SQL data types cannot.

Properly used, UDTs can enhance the robustness and clarity of your database design, making it easier to maintain and scale your applications. Remember, the goal is to make your data work for you, ensuring it is stored, accessed, and manipulated in the most effective way possible. UDTs, when used correctly, can be a cornerstone in achieving this goal.

Find out about our Business Intelligence Consultancy Service.

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

We select our Business Analytics Timesavers from our day-to-day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Blog

Blog Posted by David Laws

LinkedIn