SQL Server talking about user-defined table types

1.1 Introduction

In SQL Server, a user-defined table type refers to a type defined by the user that represents a table structure definition. You can use a user-defined table type to declare a table-valued parameter for a stored procedure or function, or to declare a table variable that you want to use in a batch or in the body of a stored procedure or function.

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

1.2 Using 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] () not NULL,

[LastName] [nvarchar] () not NULL,

[Email] [nvarchar] () not NULL,

[Phone] [varchar] (a) 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) O N [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] () not NULL,

[LastName] [nvarchar] () not NULL,

[Email] [nvarchar] () not NULL,

[Phone] [varchar] (a) Not NULL,

PRIMARY KEY CLUSTERED

(

[ContactID] Asc

) with (Ignore_dup_key = off)

)

Go

SQL Server Management Studio sees the following results:

Now we start writing stored procedures using user-defined table types

Use [Contacting]

Go

CREATE PROCEDURE [dbo]. [Insertcontacts]

@contacts as Contactstruct READONLY

As

INSERT into Contacts (ContactID, FirstName, LastName, Email, Phone)

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

return 0

1.3 Use Restrictions (unfortunately, you cannot return data in table-valued parameters.) Table-valued parameters are parameters that can only be entered, and OUTPUT keywords are not supported. )

Note that user-defined table types use the following restrictions:

1. You cannot modify a user-defined table type definition after it has been created. (Don't understand why not to change)

2. You cannot call a user-defined function in the definition of a computed column of a user-defined table type.

3. You cannot create a nonclustered index on a user-defined table type unless the index is the result of creating a primary KEY or a unique constraint on a user-defined table type.

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