SQL Server table variables, table types, temporary tables

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