Go: The difference between temporary tables, temporary variables, and with as keywords in a SQL database creating a "temporary table"

Original link: https://www.cnblogs.com/zhaowei303/articles/4204805.html

When processing data in a SQL database, it is sometimes necessary to create temporary tables, put the resulting set of queries into a temporary table, and then manipulate the data.

There are several ways to create a temporary table (a logical temporary table, which may not necessarily be a database):

1.with Temptablename as Method (appears after 05):

With temptable as does not actually create a temporary table, just the subquery part (subquery factoring), which defines a SQL fragment that will be used by the entire SQL statement. Sometimes it is to make the SQL statement more readable, or it may be in different parts of union all as part of providing data. Especially useful for union all. Because each part of union all may be the same, but if each part goes through it, the cost is too high, so you can use the with as phrase, as long as you execute it again.

Example:

with Snd
    as
    (
        select * from category 
        where cgtype=2
        and parentid=@FstCgid
    ),
    thrd
    as
    (
        select c.* from category c
        inner join Snd s
        on c.parentid=s.cgid
        and c.cgtype=3
    ),
    forth
    as
    (
        select c.* from category c
        inner join thrd t
        on c.parentid=t.cgid
        and c.cgtype=4
    )

Note: The above code, if preceded with snd "decalare @FstCgid int = 1234", must be ";" End, otherwise will be error;

Note: If the table name defined by the with as phrase is called more than two times, the optimizer automatically places the data obtained with the as phrase into a temp table, if it is called only once. The hint materialize, however, is to force the data in the with as phrase into a global temporary table.

2. Temporary table methods

A temporary table is similar to a permanent table, except that it is created in tempdb and only disappears after the end of a database connection or by a SQL command, otherwise it will persist (after the temporary table is generally created, if you do not pass the drop Table operation, the second time can no longer be created ). Temporary tables generate the system logs of SQL Server when they are created, although they are in tempdb and are allocated in memory, they also support physical disks, but the user cannot see the files on the specified disk.

Temporary tables are both local and global, and the name of the local temporary table is prefixed with "#", which is visible only in the local current user connection and is deleted when the user disconnects from the instance. The names of global temporary tables are prefixed with "# #" and are visible to any user after they are created, and are deleted when all users referencing the table are disconnected.

Example:

if object_id(‘tempdb..#tempCategory‘) is not null drop table #tempCategory
    create table #tempCategory(
        num int,
        CGName varchar(50),
        CGID int,
        PartnerID int,
        UpdTime datetime,
        Operator varchar(50)
    )

Note: Some features of the temp table:

1) Add, modify, and delete columns. For example, the name, length, data type, precision, scale, and nullability of a column can be modified, but there are some limitations.

2) You can add or remove primary key and foreign key constraints.

3) You can add or remove UNIQUE and CHECK constraints and DEFAULT definitions (objects).

4) You can use the IDENTITY or ROWGUIDCOL property to add or remove identifier columns. Although the ROWGUIDCOL property can be added to an existing column or deleted from an existing column, there can be only one column in the table that can have that property at any time.

5) The columns selected in the table and table are registered as full-text indexes.

3. Table variable Methods

A table variable creates a syntax similar to a temporary table, and the difference is that it must be named when it is created. Table variables are one of the variables, the table variable is also divided into local and global two, the name of the surface variable is "@" prefix, only in the local current user connection can be accessed. The name of the global table variable is prefixed with "@@", which is generally the global variable of the system, as we commonly use, such as @ @Error represents the wrong number, @ @RowCount represents the number of rows affected.

Example:

DECLARE @News Table 
  ( 
  News_id int NOT NULL, 
  NewsTitle varchar(100), 
  NewsContent varchar(2000), 
  NewsDateTime datetime 
  )

The difference between the above three methods:

The difference between a temporary table and a table variable:

1) The table variable is stored in memory, when the user accesses the table variable, SQL Server does not generate the log, and in the temporary table is generated log;

2) in a table variable, a nonclustered index is not allowed;

3) The table variable is not allowed to have default defaults, and does not allow constraints;

4) The statistical information on the temporary table is sound and reliable, but the statistics on the table variable are unreliable;

5) There is a locking mechanism in the temporal table, and there is no mechanism for locking in the table variable.

Selection of temporary tables and table variables:

1) The main thing to consider when using table variables is the pressure on the memory of the application, and if the code runs a lot of instances, pay special attention to memory variables ' memory consumption. We use table variables for smaller data or for recommended calculations. If the result of the data is large, in the code for the temporary calculation, when the selection is not a grouping of aggregations, you can consider using table variables.

2) generally for large data results, or because the statistical data to facilitate better optimization, we recommend the use of temporary tables, but also to create an index, because the temporary table is stored in tempdb, the general default allocation of less space, you need to tune tempdb to increase its storage space.

Use of CTE and with as phrases to improve SQL query performance:

Cet is much more efficient than table variables!

Table variables actually use temporary tables, which increases the additional I/O overhead, so that table variables do not work well for large data volumes and queries frequently.

2. The other way of writing is not to splice SQL statements, directly write on the line

SELECT * into #TempProducts from [dbo]. [Sys_product];

SELECT * from #TempProducts;

3. The temporary table for a # is transaction-level, meaning that a temporary table exists only in one transaction.

Go: The difference between temporary tables, temporary variables, and with as keywords in a SQL database creating a "temporary table"