Ocean studio-website construction expert: summary of the use of ms SQL Server temporary tables and cursors

1. 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

 

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

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