SQL Server table variables, table types, temporary tables

Source: Internet
Author: User

Temporary tables in SQL Server are divided into session temporary tables and permanent temporary tables. The session temp table is automatically deleted after the session ends, and the permanent temporary table is basically no different from the basic table, and it needs to be displayed by calling drop to delete it.

Create a temporary table

Create a session temp table

CREATE TABLE #table_name (column_name datatype constraint_name[...]);

Create a permanent temporary table

CREATE TABLE # #table_name (column_name datatype constraint_name[...]);

Constraints on temporary tables can be established after the table is created, using the

ALTER TABLE TABLE_NAME ADD CONSTRAINT Contraint_name statement creation.

Table type

A table type is a user-defined type that allows the user to create the type of table they need, and it is straightforward to create the table structure and constraints in advance, and then create table variables directly from the table type.

Create a table type

Create Type Type_name as table (column_name datatype constraint_name[...);

The constraints in the table here must be created when the type is created, and you cannot use the ALTER statement.

Declare table variables according to table type

DECLARE @table_variable type_name;

Table variables

A table variable is a type of data that has the structure of a table and the functionality of some tables. It can be queried, inserted, updated, deleted. It is important to note that table variables cannot be used with select: INTO statement Insert data

But temporary tables can. Table variables can be used in combination with stored procedures, functions, and other blocks. Table variables are consistent with the methods and declarations of other basic variable types.

Declaration Table variables

Declare @table_variable table (column_name datatype constraint_name[...);

SQL Server table variables, table types, temporary tables

Related Article

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.