Create User-Defined Table Type

Introduction

My previous article demonstrated, how to create User-Defined Data Type. In this article we will discuss about User-defined Table Type and will discuss how to create in SQL.

Getting Started

User-Defined Table Types represent tabular information. They are used as parameters when you pass tabular data into stored procedures or User-Defined functions. User-Defined tables cannot be used to represent columns in a database table.

This concept is introduced in SQL Server 2008, before this version it was not possible to create User-Defined Table or User-Defined Table types in SQL. As like User-Defined data type, the User-Defined Table types are stored in sys.tyles table.

To identify User-Defined Table types the sys.types has one column called is_table_type, the sys.types table stores value as 1 for User-Defined Table types. All the User-Defined Table types can be listed out in SQL using below SQL query.

 SELECT * FROM SYS.types WHERE is_table_type=1  

User-Defined Table types cannot be altered after they are created. It does not support the Alter method. User-defined table types supports means it can have check constraints but as this type is not alterable.

The syntax for creating User-Defined Table types is same as creating User Table but bit difference, see the below two syntax which are used for creating User-Defined Table type and Use Table.

 -- ================================  
 -- Create User-defined Table Type  
 -- ================================  
 -- Create the data type  
 CREATE TYPE <Table_Name> AS TABLE   
 (  
      <Column_Name1> <DataType> NOT NULL,  
      <Column_Name2> <DataType> NULL,  
      <Column_Name[N]> <DataType  
 )  
 GO  

 -- ================================  
 -- Create User-defined Table  
 -- ================================  
 CREATE TABLE <Table_Name>  
 (  
      <Column_Name1> <DataType> NOT NULL,  
      <Column_Name2> <DataType> NULL,  
      <Column_Name[N]> <DataType  
 )  
 GO  

As like SQL Table you can SELECT, INSERT, UPDATE, DELETE a record from User-Defined Table types. The syntax is the same as we are using while using it in the table. The User-Defined Table can be used with the JOIN query as well.

Create User-Defined Table Type using T-SQL Command

1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste code the below syntax and change as per your requirement. An example also given with syntax , refer the bellow example as well.

Syntax:

 -- ================================  
 -- Create User-defined Table Type  
 -- ================================  
 -- Create the data type  
 CREATE TYPE <Table_Name> AS TABLE   
 (  
      <Column_Name1> <DataType> NOT NULL,  
      <Column_Name2> <DataType> NULL,  
      <Column_Name[N]> <DataType  
 )  
 GO  

Example:

 CREATE TABLE [dbo].[Student]  
 (  
      [Student_pkey] [int] NOT NULL,  
      [Student_Name] [int] NULL,  
      [Student_CreatedOn] [datetime] NULL,  
      [Student_CreatedBy] [int] NULL,  
      [ModifiedDate] [datetime] NULL  
 )  
 GO  

Note:-This demonstration conducted in SQL server 2017

Related Articles

1. Create Table variable in SQL
2. Passing DataTable to StoredProcedure as Parameter in C# Ado.Net
3. Creating Stored Procedure with Table as Parameter in C#

Thanks