Summary of temporary tables and cursors

RelatedArticleNavigation

1. SQL server2005 Transact-SQL new weapon learning Summary-Summary
2. Index of flex and fms3 articles
3. Flexair open-source edition-global free multi-person video chat room, free network remote multi-person Video Conferencing System (jointly developed by flex and fms3) <video chat, conference Development Instance 8>

Recently usedDBThere are many opportunities,I have made some summary:

I. Temporary table

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

Temporary tables can be local or global.

 

2By comparison:

The name of a local temporary table is symbolic.(#)Headers

Only visible to the current user connection

The instance is automatically deleted when it is disconnected.

 

The name of the global temporary table is symbolic.(##)Headers

All users are visible

It is automatically deleted when all users that reference the table are disconnected.

 

 

In fact, the local temporary table isTempdbWith a unique name

For example, we useSALog on to a query analyzer and use it again.SALog on to another query Analyzer

 

In2Query Analyzer:

Use pubs

Go

Select * into # TEM from jobs

 

Are2Users created2Local temporary tables

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. In the dynamicSQLThe local temporary table created in the statement is automatically deleted after the statement is run.

Therefore, the following statements cannot obtain the result set.

Exec ('select * into # tems from job ')

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

 

 

 

 

 

 

Favorites and sharing

Add QQ bookmarks to Baidu souzang {
Function onclick ()
{
Window. Open ('HTTP: // myweb.cn.yahoo.com/popadd.html? Url = '+ encodeuricomponent (document. location. href) + '& Title =' + encodeuricomponent (document. title), 'yahoo ', 'scrollbars = Yes, width = 440, Height = 440, Left = 80, Top = 80, status = Yes, resizable = Yes ');
}
} "> Add to Yahoo favorites

RSS subscribe to me What is RSS?

Dongguan. Net Club

Welcome to join