Use temporary tables

1. Introduction to temporary tables

SQL Server temporary tables are similar to permanent tables, but they are also different. The following describes SQL Server temporary tables for your reference.

A temporary SQL Server table is similar to a permanent table, but it is created in tempdb. It disappears only after a database connection is completed or is dropped by the SQL command, otherwise, it will always exist. Temporary tables generate SQL Server System logs when they are created. Although they are reflected in tempdb, they are allocated in memory and support physical disks, but the user cannot see the file in the specified disk.

SQL Server temporary tables can be divided into local and global tables. The names of local temporary tables are prefixed with "#" and are only visible in local user connections, the user is deleted when the instance is disconnected. The name of the global temporary table is prefixed with "#". After the table is created, it is visible to all users. When all users that reference the table are disconnected, the table is deleted.

Here is an example of creating a temporary SQL Server table:

Create Table DBO. # News (news_id int not null, newstitle varchar (100 ),

Newscontent varchar (2000), newsdatetime datetime)

SQL Server temporary tables can create indexes or define statistical data. Therefore, you can use the Data Definition Language (DDL) statement to block restrictions and constraints added to temporary tables, and reference integrity, such as primary key and foreign key constraints. For example, to add a default getdata () current date value for the # News table field newsdatetime and add a primary key for news_id, we can use the following statement:

Alter table dbo. # news Add constraint [df_newsdatetime] default (getdate () for [newsdatetime], primary key clustered ([news_id]) on [primary] Go SQL Server temporary tables can be modified after they are created, including:

1) Add, modify, and delete columns. For example, the column name, length, data type, precision, number of decimal places, and null can be modified, but there are some restrictions.

2) You can add or delete primary keys and foreign key constraints.

3) You can add or delete unique and check constraints and default definitions (objects ).

4) You can use the identity or rowguidcol attribute to add or delete an identifier column. Although the rowguidcol attribute can be added to or deleted from an existing column, only one column in the table can have this attribute at any time.

5) The selected columns in the table and table have been registered as full-text indexes.

1. A temporary table is a table with the "#" prefix added when you create a table. It is characterized by process independence. Only the process owner has the table access permission. Other users cannot access the table;
2. Although the temporary tables created by different user processes have the same name, they do not have any relationship with each other. In sqlserver, the special naming mechanism is used to ensure the process independence of the temporary table.
3. Orders and Purchase Intentions are generally not stored in a real "temporary table", but an actual common table. The so-called "temporary table" is just a name. As a user process ends, the real temporary table is automatically cleared, while the order and purchase intention data are usually cleared regularly, so it must be saved in a common table, have the persistence characteristics of data (the persistence of data is the most lacking in temporary tables ).
4. The real temporary table uses the temporary tablespace of the database, which is automatically maintained by the database system, thus saving the table space. In addition, temporary tablespace generally uses virtual memory, which greatly reduces the number of hard disk I/O operations and improves system efficiency.

Data is automatically cleared after the transaction or session is completed, so you do not have to remember to delete the data after it is used up.
The data is visible during the current session. Other sessions can only see their structure, but only their own data. The data of each session does not interfere with each other. For example, create a temporary table t_tmp (a number primary key, B Number) statically)
User A can have data. User B can have data.
1 2 1 2
2 3 3 4
3 4 6 7
4 5
A) Select count (*) from t_emp; the result is 4 (B's data cannot be seen)
B) Select count (*) from t_emp; the result is 3 (it cannot be seen from data of)
They have two identical records, but since the data only applies to the current session period, the primary key does not limit them and only limits the unique primary key of A under the current user.
The above non-interference feature can be used to process intermediate computing processes. If a regular table can only be operated by one session at a time, the locked resource will not be accessed by other sessions.

 

 

Ii. Use the SQL Server temporary table to prevent repeated User Logon

When developing business software, we often encounter the following problem: how to prevent users from repeatedly logging on to our system? Especially for banks or financial departments, it is necessary to restrict users to log on multiple times as their employee IDs.

Some people may say that they should add a field in the user information table to determine the logon status of the user's employee ID, write 1 after logon, write 0 when exiting, and determine whether the flag is 1 when logon, if yes, the user's employee ID is not allowed to log on. However, this will inevitably lead to new problems: in the case of unpredictable phenomena such as power outages, the system exits abnormally and cannot set the mark position to 0, so next time you log on with this user's employee ID, you cannot log on. What should I do?

Maybe we can change our thinking: What can be automatically recycled by the system after the connection is disconnected? By the way, SQL Server temporary tables have this feature! However, in this case, we cannot use a local SQL Server temporary table. Because a local temporary table is an independent object for each connection, we can only use a global temporary table for our purpose.

Now, the situation is clear. We can write a simple stored procedure like the following:

1. Create procedure gp_findtemptable

2.

3./* search for a global temporary table named after the operator's employee ID

4. * If not, set the out parameter to 0 and create the table. If yes, set the out parameter to 1.

5. * after the connection is disconnected, the global temporary table will be automatically reclaimed by SQL Server.

6. * In the event of an accident such as power failure, the global temporary table still exists in tempdb,

7. But it is no longer active.

8. * When the object_id function is used to determine whether the object does not exist.

9 .*/

10. @ v_userid varchar (6), -- operator ID

11. @ I _out int out -- output parameter 0: Not Logged On 1: logged on

12.

13. Declare @ v_ SQL varchar (100)

14. If object_id (''' tempdb. DBO. # ''' + @ v_userid) is null

15. Begin

16. Set @ v_ SQL = ''' CREATE TABLE # ''' + @ v_userid +

17. ''' (userid varchar (6 ))''''

18. Exec (@ v_ SQL)

19. Set @ I _out = 0

20. End

21. Else

22. Set @ I _out = 1

In this process, we can see that if the global temporary table named after the user's employee number does not exist, a new table will be created and the out parameter is set to 0, if it already exists, set the out parameter to 1.

In this way, we useProgramWhen this process is called, if the out parameter obtained is 1, we can jump out of a message and tell the user to say "Sorry, this employee ID is in use !"

Sample judgment method:

1. Select @ stmpwarea = "tempdb .. [# marwarea" + @ computername + "]"

2. If exists (select * From tempdb .. sysobjects where id = object_id (@ stmpwarea) and type = "U ")

3. Begin

4. Set @ stmpwarea = "[# marwarea" + @ computername + "]"

5. Exec ("Drop table" + @ stmpwarea)

6. End

7. Else

8. Set @ stmpwarea = "[# marwarea" + @ computername + "]"

@ Stmpwarea is the name of the SQL Server temporary table, which is operated by Exec during the process.

 

 

Iii. Correct deletion method for SQL Server temporary tables

1. Incorrect delete operation:

-- The temporary table deletion operation is incorrect because the database is different.
If exists (select * From sysobjects where object_id = object_id (n' [DBO]. [# temptable] ') and type in (n'u '))
Begin
Drop table [DBO]. [temptable]
End
-- The temporary table deletion operation is incorrect because the temporary table name has changed.
If exists (select * From tempdb. DBO. sysobjects where id = object_id (n' [# temptable] ')
Begin
Drop table # temptable
End

2. Correct deletion method:

-- Correct temporary table deletion operation
If object_id ('tempdb .. # temptable') is not null begin
Drop table # temptable
End

 

4. Use a temporary SQL Server table to merge strings

Processed data

1. Create Table Tb (col1 varchar (10), col2 INT)

2. Insert TB select 'A', 1

3. Union all select 'A', 2

4. Union all select 'B', 1

5. Union all select 'B', 2

6. Union all select 'B', 3

Merge Processing

1. Select col1, col2 = cast (col2 as varchar (100 ))

2. into # T from TB

3. Order by col1, col2

4. Declare @ col1 varchar (10), @ col2 varchar (100)

5. Update # t set

6. @ col2 = case when @ col1col1 = col1 then @ col2 + ',' + col2 else col2 end,

7. @ col1col1 = col1,

8. col2 = @ col2

9. Select * from # T

Updated SQL Server temporary table

1. col1 col2

2 .-

3. A 1

4. A 1, 2

5. B 1

6. B 1, 2

7. B 1, 2, 3

8 .*/

Get the final result

1. Select col1, col2 = max (col2) from # T group by col1

2./* result

3. col1 col2

4 .-

5. A 1, 2

6. B 1, 2, 3

7 .*/

Delete Test

1. Drop table TB, # T

2. Go

 

PS:

Cast: explicitly converts a certain data type expression to another data type. Cast and convert provide similar functions.
Syntax
Use cast:
Cast (expression as data_type)