SQL Server user-defined table types

1.1 Introduction

In SQL Server, the User-Defined table type refers to the type defined by the user-defined table structure. You can use a user-defined table type to declare Table value parameters for stored procedures or functions, or declare the table variables you want to use in batch processing or in the body of stored procedures or functions.

To CREATE a user-defined table TYPE, use the create type statement. To ensure that data of the User-Defined table type meets specific requirements, you can create unique constraints and primary keys for the User-Defined table type.

1.2 examples

Create a database table before creating a user-defined table Type

USE [Contacting]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

Create table [dbo]. [Contact] (

[ContactID] [uniqueidentifier] not null,

[FirstName] [nvarchar] (80) not null,

[LastName] [nvarchar] (80) not null,

[Email] [nvarchar] (80) not null,

[Phone] [varchar] (25) NULL,

[Created] [datetime] not null,

PRIMARY KEY CLUSTERED

(

[ContactID] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Alter table [dbo]. [Contact] add default (getdate () FOR [Created]

GO

Then create a user-defined table Type InsertContacts

USE [Contacting]

GO

Create type [dbo]. [ContactStruct] as table (

[ContactID] [uniqueidentifier] not null,

[FirstName] [nvarchar] (80) not null,

[LastName] [nvarchar] (80) not null,

[Email] [nvarchar] (80) not null,

[Phone] [varchar] (25) not null,

PRIMARY KEY CLUSTERED

(

[ContactID] ASC

) WITH (IGNORE_DUP_KEY = OFF)

)

GO

SQL Server Management Studio displays the following results:

Now we start to use User-Defined table types to write stored procedures.

USE [Contacting]

GO

Create procedure [dbo]. [InsertContacts]

@ Contacts AS ContactStruct READONLY

AS

Insert into Contact (ContactID, FirstName, LastName, Email, Phone)

SELECT ContactID, FirstName, LastName, Email, Phone FROM @ contacts;

RETURN 0

1.3 restrictions (unfortunately, data cannot be returned in Table value parameters. Table value parameters are only input parameters. OUTPUT keywords are not supported .)

Note that the following restrictions apply to user-defined table types:

1. A user-defined table type definition cannot be modified after it is created. (Why can't I modify it)

2. You cannot call user-defined functions in the definition of computing columns of user-defined table types.

3. A non-clustered index cannot be created for the User-Defined table type, unless the index is the result of creating a primary key or UNIQUE constraint for the User-Defined table type.

4. the user-defined table type cannot be used as a column in the table or a field in the structured user-defined type.