SQL Server temporary table and cursor usage summary, SQL Server

SQL Server temporary table and cursor usage summary, SQL Server

1. Temporary table

Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb. They are automatically deleted when they are no longer used.
Temporary tables can be local or global.

2 Comparison:

The name of a local temporary table starts with a symbol (#).
Only visible to the current user connection
The instance is automatically deleted when it is disconnected.
The name of the global temporary table starts with a symbol (#).
All users are visible
It is automatically deleted when all users that reference the table are disconnected.
In fact, the local temporary table has a unique name in tempdb.
For example, log on to a query analyzer using sa, and then log on to another query analyzer using sa.

In both query analyzers, we allow the following statements:

use pubsgoselect * into #tem from jobs

Two local temporary tables are created for two users respectively.
We can see from the following query statement

SELECT * FROM [tempdb].[dbo].[sysobjects] where xtype='u' 

Determine the existence of a temporary table:

if object_id('tempdb..#tem') is not null begin  print 'exists' end else begin  print 'not exists' end 

Note:
1. The local temporary table created in the dynamic SQL statement is automatically deleted after the statement is run.
Therefore, the following statements cannot obtain the result set.

exec('select * into #tems from jobs')select * from #tems

2. Temporary tables used in the stored procedure are automatically deleted after the process is completed.
However, explicit deletion is recommended, which is beneficial to the system.

Ii. Cursor
The cursor can also be local or global.
Local cursor: used only in the declaration stage
Global cursors: they can be declared and used outside the trigger.

Judgment existence:

If CURSOR_STATUS ('global', 'cursor name') =-3 and CURSOR_STATUS ('local', 'cursor name ') =-3 begin print 'not exists' endSELECT * FROM [tempdb]. [dbo]. [sysobjects] where xtype = 'U'

Determine the existence of a temporary table:

if object_id('tempdb..#tem') is not null begin  print 'exists' end else begin  print 'not exists' end 

Note:
1. The local temporary table created in the dynamic SQL statement is automatically deleted after the statement is run.
Therefore, the following statements cannot obtain the result set.

exec('select * into #tems from jobs')select * from #tems

2. Temporary tables used in the stored procedure are automatically deleted after the process is completed.
However, explicit deletion is recommended, which is beneficial to the system.

Ii. Cursor
The cursor can also be local or global.
Local cursor: used only in the declaration stage
Global cursors: they can be declared and used outside the trigger.

Judgment existence:

If CURSOR_STATUS ('global', 'cursor name') =-3 and CURSOR_STATUS ('local', 'cursor name ') =-3 begin print 'not exists' endSELECT * FROM [tempdb]. [dbo]. [sysobjects] where xtype = 'U'

Determine the existence of a temporary table:

if object_id('tempdb..#tem') is not null begin  print 'exists' end else begin  print 'not exists' end 

Note:
1. The local temporary table created in the dynamic SQL statement is automatically deleted after the statement is run.
Therefore, the following statements cannot obtain the result set.

exec('select * into #tems from jobs')select * from #tems

2. Temporary tables used in the stored procedure are automatically deleted after the process is completed.
However, explicit deletion is recommended, which is beneficial to the system.

Ii. Cursor
The cursor can also be local or global.
Local cursor: used only in the declaration stage
Global cursors: they can be declared and used outside the trigger.

Judgment existence:

If CURSOR_STATUS ('global', 'cursor name') =-3 and CURSOR_STATUS ('local', 'cursor name') =-3 begin print 'not exists' end