SQL Server Temp Table utility Daquan

We are going to discuss with you today is the SQL Server temporary table of the utility, if you are not very familiar with the SQL Server temporary table, the following article is a detailed description of its related content, hope will bring you some help in this regard.

Introduction:

Temporary data tables, which we often meet when stored.

The client can be useful for Delphi's clientdataset memory table, but Clientdataset is similar to table, not supported by SQL language.

Of course, temporary tables can also be useful. Sometimes we avoid the hassle of the life cycle of SQL Server temporary tables, and more use the actual tables for temporary use. What a tempxxx like to tell us they are temporary tables. There is no problem creating the release.

So what is the periodicity of the temporary table? Do you have any special usage?

1. The difference between a global temporary table and a temporary table: The field of view is different.

The Global temporary table is, of course, using # #打头的表格, and the normal temporary table starts with #. Their cycles should be born with the birth of a connection, the connection, and die with the disconnection of connection. They differ in different places, namely the field of view is different.

Global table, all authorized connection can be seen. But ordinary SQL Server temporary tables (local temp tables) are only created by connection that can be seen. Specifically, the Isql.exe of SQL Server is a connection per connection.

For example, if an application App.exe has only adoconnection connection to SQL Server data, the adoquery in this connection or the global table # created by the stored procedure # #temp1, The table can be used by another app.exe or other appx.exe.

The difference is the ordinary temporary table, by the connection in the App.exe connection such as ADOConnection Adoquery set up a temporary table #temp, The app.exe of all the connected programs can be seen and accessed, but appx.exe or otherwise cannot be seen.

Traps: If an application app.exe has stored procedures and tadoquery connections to the same adoconnection. Remember that a temporary table (non-global) created with a stored procedure cannot be accessed by the tadoquery under this connection. The reason: the stored procedure itself is server-side execution and should be the connection of the server.

2. Storage and establishment of global tables or SQL Server temporary tables.

As we all know, temporary tables or global tables are stored in the tempdb database of SQL Server, we use # when we access the table, but the actual process is in the user table in tempdb to #xxx_________ 00000000xxx mode Storage (whether it is a process ID, not sure, and don't want to know). You can see them through the Isql.exe tempdb.

3. Headache Initialization Table

Initialize the table, why the headache, after all, use drop something. Or to judge the existence of a more troublesome. It's very simple, actually. In addition, the establishment of temporary tables, are boring SQL statements, how to deal with.

Method One: Of course, the use of stored procedures. You can write SQL statements directly or write EXEC (@v_sql).

Method Two: Save SQL Server Temporary table to the data inside memo inside, read, execute directly.

Judgment method One: You can use the usual exists function.

For example:

1. if exists (SELECT * from tempdb. sysobjects where id = object_id (@sTmpWareA) and type = "U")

Judging method Two:

1. If object_id (' tempdb.dbo.## ' [email protected]_userid) is null

Application Method One: (thank Aman, confused)

A statistical SQL Server temporary table that executes the stored procedure using Tadoproc, as follows:

1. CREATE PROCEDURE initcreatecoawardtable as
2. BEGIN
3. IF Not (object_id (' tempdb.dbo. #Tmp_Detail ') is null)
4. drop table tempdb.dbo. #Tmp_Detail
5. CREATE TABLE tempdb.dbo. #Tmp_Detail (
6. [ID] [int] IDENTITY (1, 1) not NULL,

[Document Category] [varchar] (COLLATE) Chinese_prc_ci_as NULL,

[Document Number] [varchar] (COLLATE) Chinese_prc_ci_as NULL,

（。。 It's more complicated here ... Omitted... ）

1. SELECT * from tempdb.dbo. #Tmp_Detail
2. END
3. GO

The client uses Tadoproc to execute this stored procedure,

1. With Sproc do
2. Begin
3. Try
4. Close;
5. procedurename:=spname;
6. Parameters.refresh;
7. Prepared;
8. Execproc;

open;//This sentence cannot be omitted otherwise, the database name ' is ignored, and the object in tempdb is referred to as an error.

1. Except
2. Close;
3. Exit;
4. End
5. result:=True;
6. End

In this way, your tadoproc can use the Append,insert statement arbitrarily, the better is not need to clean up, and the method is flexible. Better to be a bit very effective at handling concurrency. Of course, some people are still using the actual table to replace it, using the session to deal with the problem, you have to recycle. Quite troublesome. And the flexibility is much worse.

The following are examples of applications reproduced:

Prevent users from repeating logins by using the global temporary table of SQL Server

When we develop business software, we often encounter such a problem: how to prevent users to repeatedly login to our system? Especially for banks or financial departments, but also to restrict users to their work number as many times logged in.

May be said in the User Information table to add a field to determine the status of the user sign in, write 1 after logging on, write 0 on exit, and log in to determine whether its flag bit is 1, if the user is not allowed to log in. But that will inevitably bring new problems: such as the occurrence of unpredictable phenomena such as power outages, the system is not normal exit, unable to mark the location of 0, then the next time the user work number login is not logged in, how to do?

Perhaps we can change the idea: What is the connection after the disconnection can be automatically collected by the system? Yes, the SQL Server temp table has this feature! But this is not the case with local temporary tables, because local temporary tables are a separate object for each connection, so we can only use global SQL Server temporary tables to achieve our purpose.

Well, the situation is clear, and we can write a simple stored procedure like this:

1. CREATE PROCEDURE gp_findtemptable--2001/10/26 21:36 Zhuzhichao in Nanjing

/* Look for a global temporary table named for the operation employee number

* If none, set the out parameter to 0 and create the table, if any, set the out parameter to 1

* After the connection is disconnected, the global temp table is automatically reclaimed by SQL Server

* In the event of an accident such as a power outage, the global temp table still exists in tempdb but has been inactive

* The OBJECT_ID function is used to determine that it does not exist. */

@v_userid varchar (6),--Operation employee number

@i_out int out--output parameter 0: No login 1: Already logged in

1. As
2. DECLARE @v_sql varchar (100)
3. If object_id (' tempdb.dbo.## ' [email protected]_userid) is null
4. Begin
5. SET @v_sql = ' CREATE TABLE # # ' [email protected]_userid+ ' (userid varchar (6)) '
6. EXEC (@v_sql)
7. SET @i_out = 0
8. End
9. Else
10. SET @i_out = 1

In this process, we see that if the global SQL Server temporary table named after the user work does not exist, the process will go to create one and set the out parameter to 0, and if it already exists, set the out parameter to 1.

Thus, when we call the procedure in our application, if we get an out parameter of 1 o'clock, we can simply jump out of a message and tell the user that "I'm sorry, this work number is being used!" ”

Examples of judging methods:

2. SELECT @stmpwarea="tempdb. [# #MARWareA "+ @ComputerName +"] "
4. if exists (SELECT * from tempdb. sysobjects where id = object_id (@sTmpWareA) and type = "U")
6. Begin
8. SET @stmpwarea="[# #MARWareA" + @ComputerName + "]"
10. EXEC ("drop table" [email protected])
12. End
14. Else
16. SET @stmpwarea="[# #MARWareA" + @ComputerName + "]"

@sTmpWareA is the name of the SQL Server staging table, which is used by exec to manipulate

From:http://database.51cto.com/art/201007/213431.htm

SQL Server Temp Table utility Daquan