User-Defined Table Types
UDT stands for "User-Defined Data Type." It is a feature in database management systems that allows users to create custom data types to suit their specific needs. While most database systems come with predefined data types like integers, strings, dates, etc., UDTs offer the flexibility to define new data types that can represent more complex or specialized information.
User-Defined Data Types (UDTs) can be used in various scenarios in a database to organize, standardize, and manage custom data types.
When creating a UDT, you can combine existing data types into a single logical unit. For example, if you need to store information about a person's address, you can create a UDT called "Person" that encapsulates these attributes into a single data type.
Example of Creating a User-Defined Table Type (UDTT) named "address_udt"
CREATE TYPE address_udt AS TABLE (
street VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
postal_code VARCHAR(10)
);
Insert data into the table variable and Select the data from the table variable.
-- Declare a table variable of the UDTT type
DECLARE @addresses address_udt;
-- Insert data into the table variable
INSERT INTO @addresses (street, city, state, postal_code)
VALUES
('123 Main St', 'Noida', 'UP', '201301'),
('456 house', 'Delhi', 'Delhi', '231521');
-- Display the contents of the table variable
SELECT * FROM @addresses;
Drop a User-Defined Table Type (UDTT) named "address_udt".
DROP TYPE address_udt;
Retrieve User-Defined Types from the Database.
SELECT *
FROM sys.table_types
WHERE is_user_defined = 1;
Retrieve User-Defined Types from the Database by name.
SELECT *
FROM sys.table_types
WHERE is_user_defined = 1 AND name = 'address_udt';
Storing Personal Details with Addresses using User-Defined Table Type (UDT) in Stored Procedure and how to execute the stored procedure.
-- First, create a table to store the main data
CREATE TABLE PersonTable (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100),
FatherName VARCHAR(100),
Age INT,
Street VARCHAR(100),
City VARCHAR(50),
State VARCHAR(50),
Postal_Code VARCHAR(10)
);
--Create a User-Defined Table Type (UDTT) named "address_udt"
CREATE TYPE address_udt AS TABLE (
street VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
postal_code VARCHAR(10)
);
--Create the procedure to insert personal details along with addresses
Create PROCEDURE InsertDataWithAddress
@Name VARCHAR(100),
@FatherName VARCHAR(100),
@Age INT,
@Address Address_UDT READONLY
AS
BEGIN
DECLARE @Street VARCHAR(100);
DECLARE @City VARCHAR(50);
DECLARE @State VARCHAR(50);
DECLARE @Postal_Code VARCHAR(10);
-- Assume that the UDT has only one row. You can modify accordingly if needed.
SELECT TOP 1 @Street = Street, @City = City, @State = State, @Postal_Code = Postal_Code FROM @Address;
-- Insert the main data into MainTable
INSERT INTO PersonTable (Name, FatherName, Age, Street, City, State, Postal_Code)
VALUES (@Name, @FatherName, @Age, @Street, @City, @State, @Postal_Code);
END;
--To use this procedure, you can insert data into the address_udt table type and call the persondetail procedure as follows:
-- Declare and populate the UDT variable for the address
DECLARE @MyAddress Address_UDT;
INSERT INTO @MyAddress (Street, City, State, Postal_Code)
VALUES ('123 Main St', 'Noida', 'UP', '201310');
-- Insert data with address details using the stored procedure
EXEC InsertDataWithAddress
@Name = 'Mukesh',
@FatherName = 'Rahul',
@Age = 30,
@Address = @MyAddress;
select * from PersonTable
User-Defined Table Type (UDTT) for storing product information and then using it in a function to retrieve data based on specific criteria.
-- First, create a table to store the main data and insert data into table
CREATE TABLE ProductTable (
ProductID INT IDENTITY(1,1) PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
Category VARCHAR(50)
);
INSERT INTO ProductTable (ProductName, Price, Category)
VALUES ('Product A', 100.00, 'Electronics'),
('Product B', 50.00, 'Clothing'),
('Product C', 20.00, 'Electronics'),
('Product D', 10.00, 'Toys'),
('Product E', 80.00, 'Clothing');
--Create a User-Defined Table Type (UDTT) named "product_udt"
CREATE TYPE product_udt AS TABLE (
ProductID INT,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
Category VARCHAR(50)
);
-- create a function that uses the UDTT to retrieve products based on a specific category:
CREATE FUNCTION GetProductsByCategory
(
@CategoryFilter product_udt READONLY
)
RETURNS TABLE
AS
RETURN
(
SELECT ProductID, ProductName, Price, Category
FROM ProductTable
WHERE Category IN (SELECT Category FROM @CategoryFilter)
);
Execute function to retrieve products by category:
DECLARE @FilterCategory AS product_udt;
INSERT INTO @FilterCategory (Category)
VALUES ('Electronics'), ('Clothing');
SELECT * FROM dbo.GetProductsByCategory(@FilterCategory);
Benefits of Using UDTTs for Complex Data Structures
- Code Reusability: UDTTs allow you to define complex data structures once and reuse them in multiple tables, functions, and procedures.
- Maintainability: Changes to the data structure can be made in one place (the UDTT definition), ensuring consistency and reducing maintenance effort.
- Clarity: UDTTs improve code readability by providing a clear structure and meaning to the data being stored.
- Performance: UDTTs can improve performance by reducing the number of columns in tables, which can be especially beneficial when dealing with sparse or optional data.
- Data Integrity: You can apply constraints and validations to the UDTT, ensuring that the stored data adheres to specific rules.
While UDTTs can be powerful, remember that they are not a replacement for proper table design and normalization. Use UDTTs where they make sense, such as for encapsulating reusable complex structures or passing structured data to functions and procedures.
Transaction History: For tracking changes to records over time, you can use UDTTs to hold historical values.
-- Create Product Table
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
StockQuantity INT
);
-- Create UDTT for Product History
CREATE TYPE product_history_udt AS TABLE (
TransactionDate DATETIME,
ProductID INT,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
StockQuantity INT
);
-- Create ProductHistory Table
CREATE TABLE ProductHistory (
HistoryID INT IDENTITY(1, 1) PRIMARY KEY,
TransactionDate DATETIME,
ProductID INT,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
StockQuantity INT
);
-- Create Procedure to Log Product Changes
ALTER PROCEDURE LogProductChange
(
@ProductID INT,
@TransactionDate DATETIME,
@ProductName VARCHAR(100),
@Price DECIMAL(10, 2),
@StockQuantity INT
)
AS
BEGIN
DECLARE @HistoryTable AS product_history_udt;
-- Insert into the history UDTT
INSERT INTO @HistoryTable (TransactionDate, ProductID, ProductName, Price, StockQuantity)
VALUES (@TransactionDate, @ProductID, @ProductName, @Price, @StockQuantity);
-- Insert into ProductHistory table
INSERT INTO ProductHistory (TransactionDate, ProductID, ProductName, Price, StockQuantity)
SELECT TransactionDate, ProductID, ProductName, Price, StockQuantity
FROM @HistoryTable;
-- Update the main Product table
UPDATE Product
SET ProductName = @ProductName,
Price = @Price,
StockQuantity = @StockQuantity
WHERE ProductID = @ProductID;
END;
-- Example Usage
DECLARE @TransactionDate DATETIME = GETDATE();
DECLARE @ProductID INT = 10;
DECLARE @ProductName VARCHAR(100) = 'Updated Product10';
DECLARE @Price DECIMAL(10, 2) = 100.99;
DECLARE @StockQuantity INT = 500;
-- Execute the LogProductChange Procedure
EXEC LogProductChange @ProductID, @TransactionDate, @ProductName, @Price, @StockQuantity;
-- View the ProductHistory Table
SELECT * FROM ProductHistory;
-- View the Product Table
SELECT * FROM Product;
User-Defined Table Type (UDTT) to perform a bulk data upload using a stored procedure.
--StudentDetails table structure:
CREATE TABLE StudentDetails (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
--Create UDTT student_udt :
CREATE TYPE student_udt AS TABLE (
StudentID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
--Create Stored Procedure for Bulk Data Upload:
CREATE PROCEDURE BulkUploadStudents
(
@Students student_udt READONLY
)
AS
BEGIN
INSERT INTO StudentDetails (StudentID, FirstName, LastName, Age)
SELECT StudentID, FirstName, LastName, Age
FROM @Students;
END;
--Use the Stored Procedure to Perform Bulk Upload:
-- Declare and populate the UDTT
DECLARE @StudentData AS student_udt;
INSERT INTO @StudentData (StudentID, FirstName, LastName, Age)
VALUES
(101, 'Aarav', 'Sharma', 20),
(102, 'Aanya', 'Verma', 22),
(103, 'Aditi', 'Patel', 21),
(104, 'Advait', 'Singh', 23),
(105, 'Aishwarya', 'Gupta', 19),
(106, 'Ananya', 'Kumar', 20),
(107, 'Anika', 'Jain', 21),
(108, 'Arnav', 'Mehta', 22),
(109, 'Avani', 'Shah', 19),
(110, 'Ayan', 'Chopra', 23),
(111, 'Dia', 'Yadav', 21),
(112, 'Esha', 'Malhotra', 20),
(113, 'Ishaan', 'Rao', 22),
(114, 'Kiara', 'Sinha', 19),
(115, 'Krish', 'Thakur', 21),
(116, 'Maahi', 'Choudhury', 22),
(117, 'Manya', 'Joshi', 20),
(118, 'Nikhil', 'Rajput', 23),
(119, 'Prisha', 'Agarwal', 21),
(120, 'Reyansh', 'Pandey', 20);
-- Call the BulkUploadStudents procedure
EXEC BulkUploadStudents @StudentData;
-- View the inserted data in the Student table
SELECT * FROM Student;
If you encounter any issues or have further questions, feel free to let me know, and I'll be glad to assist!
Thank you for reading, and I hope this post has helped provide you with a better understanding of about User-Defined Table Type.
"Keep coding, keep innovating, and keep pushing the boundaries of what's possible!
Happy Coding !!!