[Personal study notes 12-temporary tables and table variables]

TodayTemporary tables and table Variables
When we need to temporarily embody the data, you will think of a temporary table, table variable or table expression.
There are several advantages to this solution:
1. Processing temporary tables must be simpler than basic data. Especially when you use it multiple times, you can imagine how many huge projects you often find in a large table.
2. processing a temporary data set can give you a clear idea of your code. Some code is relatively long. If you process the data in different modules one by one, it will be clear. The temporary data set plays an important role here.

 

A. Temporary table

1. The temporary tables you created are all stored in tempdb. So when we use temporary datasets, we can reduce the pressure on your user database.

2. when judging whether your temporary table exists, use if object_id ('tempdb .. # t') is not null --> note that tempdb must be used here .. temporary table name.

3. The temporary table you created in one session is only valid in this session. This means that you can create a temporary table with the same name in another session. You may be surprised that the SQL internal
How does one know which temporary table is used? In fact, every time you create a temporary table, it will add an underline and a digital suffix to the table name in tempdb. For example:
MeA temporary table is created in the master and pubs databases respectively # P
Use tempdb <br/> go <br/> select name from sys. objects where name like '# P %' <br/>/* <br/> name <br/> --------------------- <br/> # P _______________________________________ 000000000011 <br/> */

4. The temporary table is only visible at the level and internal level of the created table.
What does it mean? That is to say, if a temporary table created in a stored procedure is invisible outside the stored procedure. When the stored procedure is finished, the temporary table is finished.
You can use the temporary table created on the outermost layer of the session to find another temporary table in the session. the session ends only when it is disabled. -- | of course, You can manually delete it.
 
Here we canRaises an interesting question: Since the visibility of temporary tables is inconsistent, we can create a temporary table with the same name in a session, as long as the creation level is different.
However, there are still some problems. First, we will give an example to illustrate this problem: (the example in the book is concise)
 Set nocount on; <br/> Use tempdb; <br/> go <br/> If object_id ('dbo. proc1 ') is not null <br/> drop proc DBO. proc1; <br/> go <br/> If object_id ('dbo. proc2 ') is not null <br/> drop proc DBO. proc2; <br/> go <br/> Create proc DBO. proc1 <br/> as <br/> Create Table # T1 (col1 int not null); <br/> insert into # T1 values (1 ); <br/> select * from # T1; </P> <p> exec DBO. proc2; -- note that rpoc2 is called here <br/> go </P> <p> Create proc DBO. proc2 <br/> as <br/> Create Table # T1 (col1 int null); -- # T1 is created again here <br/> insert into # T1 values (2 ); <br/> select * from # T1; <br/> go </P> <p> -- call Stored Procedure 1 <br/> exec DBO. proc1; <br/> go <br/>/* <br/> col1 <br/> ----------- <br/> 1 </P> <p> col1 <br/> ----------- <br/> 2 <br/> */<br/> 
--- There seems to be no problem. Here their table structure is the same. Note.
-- Now the situation has changed,I will modify the # T1 table structure in the second stored procedure.To see if the situation is the same.
 Alter proc DBO. proc2 <br/> as </P> <p> Create Table # T1 (col1 int null, col2 int not null); <br/> insert into # T1 values (2, 2); <br/> select * from # T1; <br/> go </P> <p> -- run, an error occurs. <br/> exec DBO. proc1; <br/> go <br/>/* <br/> col1 <br/> ----------- <br/> 1 </P> <p> message 213, level 16, status 1, process proc2, 5th rows <br/> the number of column names or provided values does not match the table definition. </P> <p> */</P> <p>

 

-- The error here occurs obviously when proc1 calls proc2. It seems that the number of columns in table # T1 is still one rather than two columns.
Explanation: Knowledge premise: when your batch processing is parsed, the temporary table you created cannot be used. It will not be parsed until it is run. if the temporary table you reference already exists, it
Instead of intelligently determining whether you have such a temporary table, you can directly parse the existing temporary table and then use the existing table in a tragic manner.
Let's go back to this question. When proc2 is called by proc1, because # Table T1 already exists in proc1, then it will parse the insert into # T1 values (2, 2) in your proc2)
In proc1, # T1 is inserted. At this time, you will report an error.

---- OurSolutionThat is: Run proc2 first and execute exec DBO. proc1; that is what you want. This is because proc2 is executed first, and its execution plan is in the cache.
This problem tells us that it is unwise to obtain a temporary table with the same name at different levels.

5. A temporary table is a part of a transaction. Therefore, whether to treat it as a part of an external transaction is an important factor in choosing a temporary table.

6. The segment statistics of the temporary table are maintained by the optimizer and can generate an optimization plan. This is also a feature different from the table variables.
Note: Just like a stored procedure, after a temporary table changes to a certain extent, it needs to be re-compiled. Here we will briefly introduce how to recompile it.
The recompile threshold value (RT) is determined based on the table type and number of rows.
A --> persistent table: WHEN n <= 500 rows, rT = 500; n> 500, rT = 500 + 0.2 * n; (n is the base of the table)
B --> temporary table: WHEN n <= 6, rT = 6; 6 <n <= 500, rT = 500; n> 500, rT = 500 + 0.2 * n
For example, if you load 6 rows in a temporary table, then when you increase the number of rows by 7th, it will cause re-compilation ~

7. dynamically change the structure of the temporary table (table structure)
Declare @ schema as varchar (1000), @ insert as varchar (1000); <br/> set @ schema = 'col1 int, col2 decimal (10, 2 )'; <br/> set @ insert = 'insert into # t42 values (10, 20.30) '; <br/> Create Table # t42 (dummycol INT ); </P> <p> -- creates a temporary table for the outer part of the session <br/> -- alter # T adds the columns you need <br/> -- alter # T deletes unnecessary columns <br/> -- insert data <br/> exec ('<br/> alter table # t42 add' + @ schema + '; <br/> alter table # t42 drop column dummycol; <br/> '+ @ insert); <br/> go <br/> select * from # t42; 
 
--- In this case, the column name or the number of provided values do not match the table definition. the modified table structure cannot be used. this is because the outer layer has another # T
--- Parsing this dynamic insert will find the # t that the session already has, and the new table structure is useless.
--Solution --
-- You can create a lower-level environment manually. I mean to add a dynamic internally, in this way, when he wants to parse it, he will go to the layer closest to him if he cannot find it. in this way, he can parse the new table structure.
 Declare @ schema as varchar (1000), @ insert as varchar (1000); <br/> set @ schema = 'col1 int, col2 decimal (10, 2 )'; <br/> set @ insert = 'insert into # t42 values (10, 20.30) '; <br/> Create Table # t42 (dummycol INT ); <br/> exec ('<br/> alter table # t42 add' + @ schema +'; <br/> alter table # t42 drop column dummycol; <br/> exec (''' + @ insert + ''') '); <br/> go <br/> select * from # t42; <br/> ---- <br/>/* <br/> col1 col2 <br/> ----------- <br/> 10 20.30 <br/> */<br/>

 

8.Global temporary table: It is used to share data between sessions without security concerns. It differs from a local temporary table in that:
A. Because multiple sessions are visible, you cannot create multiple global temporary tables with the same name;
B. the global temporary table will die at the end of the session. for example, if you create a global temporary table in a stored procedure, the stored procedure is complete, but it will not die as soon as a local temporary table.
C. if you want to keep such a global temporary table every time you use SQL, put some important things in it. at this time, you need to do 2: it does not belong to any session; it exists automatically at startup.

 Use master; <br/> go <br/> If object_id ('dbo. sp_globals ') is not null <br/> drop proc DBO. sp_globals <br/> go <br/> Create proc DBO. sp_globals <br/> as <br/> Create Table # globals <br/> (<br/> varname sysname not null primary key, <br/> Val SQL _variant null <br/>); <br/> go <br/> 

-- Mark the stored procedure with startup during execution. SQL will call this stored procedure every time it starts.
Exec DBO. sp_procoption 'SP _ globals', 'startup', 'true ';
Go
-- Then restart SQL Server
-- Add data
Set nocount on; <br/> insert into # globals values ('var1', cast ('abc' as varchar (10 ))); <br/> select * from # globals; <br/> 

-- Disable SQL and then enable
Insert into # globals values ('var2', cast ('abc' as varchar (10); <br/> select * from # globals; 
----- We can regard this global temporary table as a table that can store global variables.

 

 

B. Table Variables

1. The displayed indexes cannot be created on table variables. Only the primary key and unique constraints can be created;

2. After the table variable is declared, you cannot modify its architecture.
Declare @ s table (a int) alter table @ s add K int is not advisable. Therefore, every creation must be comprehensive, and a single blow is required.

3. You may have encountered that table variables cannot be used as column-qualified names. If the select @ s. A from @ s error occurs, you can get an alias for @ s and then define it.

4. Table variables do have physical representations in tempdb.
Select table_name from tempdb. information_schema.tables <br/> where table_name like '% # % '; <br/>/* <br/> table_name <br/> --------------- <br/> # globals <br/> */<br/> go <br/> declare @ t table (col1 INT ); <br/> insert into @ T values (1); <br/> select table_name from tempdb. information_schema.tables <br/> where table_name like '% # % '; <br/> go <br/>/* <br/> table_name <br/> -------------- <br/> # globals <br/> #0cbae877 <br/> */ 
--- Through the code above, you will find that you can only find the global temporary table we created above in the schema information table of tempdb for the first time. We have created a table variable for the second time,
--- In the query, we found that he exists, rather than in the memory, and its name format is the same as that of the temporary table. It's amazing.

5. It can only be visible at the creation level, which is the same as the normal variable. It is different from the temporary table and visible at the internal level.

6. another difference between a table variable and a temporary table is that it is not part of a transaction, that is, the operation of the table variable and the statement are part of an external transaction, so the change will not be rolled back and will take effect permanently.

7. When using Table variables:
A. when the optimizer does not need some statistics to generate an optimal plan-all rows in the table are returned; query is performed on columns with unique indexes; query by clustered indexes; at this time, only re-compilation overhead will be generated.
B. When the table is very small;
 
C. About the tempdb Database

A. This database is very important in sql2005. It stores a lot of things.
1. Temporary tables and table variables are physically stored;
2. offline data as part of the query plan;
3. Maintain the row version.

B. It is best to put the tempdb database on a separate disk, especially when tempdb is frequently used.

C. Modify the size of the tempbd database at any time according to the actual size.

D. because the tempdb database will be cleared when the SQL is restarted, pay attention to the table and function stored procedures you want to save. you can create them in the model,
They exist in your tempdb database, but this will affect the use of other libraries. There is also a method I mentioned above, called at startup.

 

 

D. Table expression.
As I mentioned above, table expressions can be used for temporary data storage. It is suitable for two scenarios:
1. to simplify the operation, the code looks concise and clear.
2. When you seldom need to access this temporary dataset.
Another point: it will not be physically or physically represented in tempdb.