SQL Server temporary table and cursor usage summary, SQL Server

Source: Internet
Author: User

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

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.