SQL Server user-defined table types

Source: Internet
Author: User
Tags management studio sql server management sql server management studio
1.1 In SQLServer, the User-Defined table type refers to the type defined by the user to indicate the 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 CREATETYPE statement.

1.1 Introduction in SQL Server, the User-Defined table type refers to the type defined by the user to indicate the 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.

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.