Differences between temporary tables and table variables: Where are views and temporary tables in SQL Server?

Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb,It is automatically deleted when it is no longer in use.

Temporary tables can be local or global. They differ in terms of name, visibility, and availability. The name of the local temporary table starts with a single digit (#). They are only visible to the current user connection and are deleted when the user is disconnected from the SQL server instance. The name of the global temporary table starts with two numeric symbols (#). After being created, the table is visible to all users. When all users that reference the table are disconnected from SQL Server, the table is deleted.

For example, if you have created an employees table, you can use the table unless it has been deleted. If a local temporary table # employees is created for a database session, only the table can be used by the session. After the session is disconnected, the table is deleted. If the # employees global temporary table is created, any user in the database can use the table. If the table is not used by other users after you create it, delete it when you disconnect it. If another user is using the table after you create the table, SQL Server will delete the table when you disconnect it and all other sessions no longer use it.

Temporary table

Temporary tables are stored in the tempdb database. All users using this SQL server instance share this tempdb, because we should ensure that the hard disk used to store the tempdb database has enough space, to make it grow on its own. it is best to store it on an independent hard disk controller. because there is no competition with other hard disk I/O.

Many of usProgramThe temporary table may be shared by multiple connections. in fact, there are two types of temporary tables in SQL Server: local temporary tables and global temporary tables. Local temporary tables are identified by the # prefix, and can only be used by the connection that creates it. the global temporary table is identified by the # prefix and can be shared with other connections.

Local temporary table

The reason that the local temporary table cannot be shared by other connections is that in SQL Server 2000, a unique character is automatically appended to the table name of the local temporary table. For example:

Create Table [# dimcustomer_test]

(

[Customerkey] [int]

, [Firstname] [nvarchar] (50)

, [Middlename] [nvarchar] (50)

, [Lastname] [nvarchar] (50)

)

Now let's take a look at the sysobjects table in tempdb. We will find that the newly created temporary table # dimcustomer_test has been suffixed:

 

Use tempdb

Go

Select name from sysobjects where name like '% dimcustomer %'

 

The result is:

Name

# Dimcustomer_test ___________________________________________________________________________________________________________ 000000000005

Global temporary table

Let's take a look at the global temporary table:

Create Table [# dimcustomer_test]

(

[Customerkey] [int]

, [Firstname] [nvarchar] (50)

, [Middlename] [nvarchar] (50)

, [Lastname] [nvarchar] (50)

)

Now let's take a look at the sysobjects table in tempdb. We will find that the newly created temporary table # dimcustomer_test is not suffixed:

 

Use tempdb

Go

Select name from sysobjects where name like '% dimcustomer %'

 

The result are:

# Dimcustomer_test ___________________________________________________________________________________________________________ 000000000005

# Dimcustomer_test

 

-- Drop test temp tables

Drop table [# dimcustomer_test]

Drop table [# dimcustomer_test]

 

The name of the created global temporary table is not identified.

 

Table Variables

Table variables and temporary tables are no different for our users, but they are different in terms of storage. Table variables are stored in the memory. therefore, the performance is better than that of temporary tables!

 

Another difference is that you need to specify an alias for the table variable when using the table variable in the table connection. For example:

 

Use adventureworksdw

Go

 

Declare @ dimcustomer_test table

(

[Customerkey] [int]

, [Firstname] [nvarchar] (50)

, [Middlename] [nvarchar] (50)

, [Lastname] [nvarchar] (50)

)

--- Insert data to @ dimcustomer_test

Insert @ dimcustomer_test

(

[Customerkey]

, [Firstname]

, [Middlename]

, [Lastname]

)

Select

[Customerkey]

, [Firstname]

, [Middlename]

, [Lastname]

From dimcustomer

 

Select [@ dimcustomer_test]. customerkey, sum (factinternetsales. orderquantity)

From @ dimcustomer_test inner join factinternetsales on

@ Dimcustomer_test.customerkey = factinternetsales. customerkey

Group by customerkey

 

Result:

 

Server: MSG 137, Level 15, state 2, line 32

Must declare the variable '@ dimcustomer_test '.

 

 

If we make changes to the preceding query, use the alias for the query (and open Io ):

----- In the follow script, we used the table alias.

 

Declare @ dimcustomer_test table

(

[Customerkey] [int]

, [Firstname] [nvarchar] (50)

, [Middlename] [nvarchar] (50)

, [Lastname] [nvarchar] (50)

)

 

Insert @ dimcustomer_test

(

[Customerkey]

, [Firstname]

, [Middlename]

, [Lastname]

)

Select

[Customerkey]

, [Firstname]

, [Middlename]

, [Lastname]

From dimcustomer

 

Select T. customerkey, F. orderquantity

From @ dimcustomer_test t inner join factinternetsales F on

T. customerkey = f. customerkey

Where T. customerkey = 13513

 

Table variables are automatically deleted by the system at the end of batch processing, so you do not need to delete them as they are displayed in the temporary table.
Table variables mainly overwrite the system memory, while temporary tables use tempdb. Table variables can be used for intermediate data storage with small data volumes. When the amount of data to be temporarily saved is large, we recommend that you use temporary tables. The specific use of table variables or temporary tables can be adjusted according to the system running status.

========================================================== ================

For example, if you create a table named employees, anyone who has the security permission to use the table in the database can use the table unless it has been deleted. If you create a local temporary table named # employees, you can only perform operations on the table and delete the table when the connection is disconnected. If you create a global temporary table named # employees, any user in the table can perform operations on the table. If the table is not used by other users after you create it, delete it when you disconnect it. If the table is used by other users after creation, SQL Server deletes the table after all users are disconnected.

========================================================== ==================

The non-index view is just a definition and does not store data. Data is retrieved from the base table only during query.

The index view stores data.

 

Both the index view and temporary table data are stored on the hard disk.

Data in the index view is stored in the database file where the view is located.

Temporary table data is stored in the database file tempdb.

This article from the Business Network (wwwbs. Net): http://wwwbs.net/blog/articleview.aspx? User = Admin & artid = 11633