Summarize the use of Sql Server temporary tables and cursors

This article mainly introduces a summary of the use of Sql Server temporary tables and cursors. Friends in need can refer to the following

1. Temporary tables

Temporary tables and Permanent tables are similar, but temporary tables are stored in tempdb and are automatically deleted when no longer used.
Temporary tables have two types: local and global.

Comparison between 2:

The name of the local temporary table starts with the symbol (#)
Only for The current user connection is visible
It is automatically deleted when the user instance disconnects
The name of the global temporary table begins with a symbol (##)
It is visible to any user
When all references The table is automatically deleted when the user disconnects
In fact, the local temporary table has a unique name in tempdb
For example, we use sa to log in to one query analyzer, and then use sa to log in to another query analyzer

We allow the following statements in both query analyzers:

use pubs
go
select * into #tem from jobs

Created 2 local temporary tables for 2 users respectively
We You can see from the following query statement

##

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

to determine the existence of the temporary table:

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

Special Tips:1. The local temporary table created in the dynamic sql statement is automatically deleted after the statement is executed
So the following statement will not get the result set

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

2. The temporary table used in the stored procedure will be automatically deleted

after the process is completed, but it is recommended to delete it explicitly, which is beneficial to the system

ii. Cursors

Cursors also have two types: local and global.
Local cursors: only used in the declaration phase
Global cursors: can be used in the process of declaring them and outside triggers

To determine the existence:

if CURSOR_STATUS('global','游标名称') =-3 and CURSOR_STATUS('local','游标名称') =-3 
begin 
 print 'not exists' 
end
SELECT * FROM [tempdb].[dbo].[sysobjects] where xtype='u'

Determine the existence of the temporary table:

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

Special tips:

1. The local temporary table created in the dynamic sql statement is automatically deleted after the statement is executed
So the following statement will not get the result set

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

2. The temporary table used in the stored procedure will be automatically deleted

after the process is completed, but it is recommended to delete it explicitly, which is beneficial to the system

ii. Cursors

Cursors also have two types: local and global.
Local cursors: only used in the declaration phase
Global cursors: can be used in the process of declaring them and outside triggers

To determine the existence:

if CURSOR_STATUS('global','游标名称') =-3 and CURSOR_STATUS('local','游标名称') =-3 
begin 
 print 'not exists' 
end
SELECT * FROM [tempdb].[dbo].[sysobjects] where xtype='u'

Determine the existence of the temporary table:

##

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

Special tips:

1. The local temporary table created in the dynamic sql statement is automatically deleted after the statement is executed

So the following statement will not get the result set

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

2. The temporary table used in the stored procedure will be automatically deleted

after the process is completed, but it is recommended to delete it explicitly, which is beneficial to the system

ii. Cursors

Cursors also have two types: local and global.

Local cursors: only used in the declaration phase
Global cursors: can be used in the process of declaring them and outside the trigger

to determine the existence:

if CURSOR_STATUS('global','游标名称') =-3 and CURSOR_STATUS('local','游标名称') =-3 
begin 
 print 'not exists' 
end

The above is the detailed content of Summarize the use of Sql Server temporary tables and cursors. For more information, please follow other related articles on the PHP Chinese website!