SQL table variables differ from temporal tables + non-cursor temporary table traversal

SQL table variables differ from temporal tables + non-cursor temporary table traversalCategory: SQL Server2009-11-27 17:01 1196 People read Comments (2) favorite reports Sqlinsert Storage SQL Servermicrosoftstatistics

Question 1: Why do I introduce table variables when I already have a temporary table?

Answer 1: table variables have the following advantages over temporal tables:

• As described in SQL Server Books Online table, table variables, such as local variables, have well-defined ranges that are automatically cleared at the end of the range.
• Table variables cause fewer recompilation of stored procedures than temporary tables.
• Transactions involving table variables maintain only the duration of updates on the table variables. Therefore, when you use table variables, you need to lock and log resources less. Because table variables have a limited scope and are not part of a persistent database, transaction rollback does not affect them.

Question 2: What does it mean to use table variables less than using temporary tables to cause a stored procedure to recompile?

Answer 2: The following article discusses some of the reasons for recompiling stored procedures:

243586(http://support.microsoft.com/kb/243586/) Troubleshooting stored procedure recompilation

The "recompilation due to some temporary table operations" section also lists some of the requirements that need to be met to avoid some problems, such as using temporary tables to cause recompilation. These restrictions do not apply to table variables.

Table variables are completely independent of the batches that create these table variables, so when you execute a CREATE or ALTER statement, there is no "reparse" and a "reparse" may occur when you use a temporary table. The temporary table requires this "reparse" to reference the table from a nested stored procedure. Table variables completely avoid this problem, so stored procedures can use compiled schedules, saving resources for processing stored procedures.

3: What are the defects of table variables?

Answer 3: It has the following drawbacks compared to the temp table:

• cannot create a nonclustered index on a table variable except for a system index created for a PRIMARY or UNIQUE constraint. This can affect query performance when compared to temporary tables that have nonclustered indexes.
• Table Variables do not maintain statistics like temporary tables. On a table variable, you cannot create statistics by automatically creating or using the Create STATISTICS statement. Therefore, when complex queries are made on large tables, the lack of statistics may hinder the optimizer's ability to determine the best plan for the query, thereby affecting the performance of the query. The
• cannot change the table definition after the initial DECLARE statement. The
• table variable cannot be used in insert EXEC (but tested by myself in sql2005 can use the Insert vartable EXEC) or SELECT into statement. The check constraints, default values, and computed columns in the
• table type declaration cannot invoke user-defined functions.
• If a table variable is created outside of an exec statement or   sp_executesql   stored procedure, you cannot use the EXEC statement or sp_executesql   a stored procedure to run a dynamic SQL Server query that references the table variable. Because table variables can only be referenced in their local scope, the EXEC statement and   sp_executesql   stored procedures will be outside the scope of the table variable. However, you can create a table variable and perform all processing in the EXEC statement or   sp_executesql   stored procedure, because the table variable local scope will be in the EXEC statement or   sp_executesql   stored procedures.

Issue 4: Compared to temporary or permanent tables, the only memory-only structure of table variables guarantees better performance because they are maintained in a database that resides on a physical disk.

Answer 4: A table variable is not a structure that exists only in memory. Because a table variable may hold more data than it can fit in memory, it must have a location on the disk to store the data. Similar to temporary tables, table variables are created in the tempdb database. If there is enough memory, both the table variable and the staging table are created and processed in memory (data cache).

5: Do I have to use table variables instead of temporary tables?

Answer 5: The answer depends on the following three factors:

• The number of rows inserted into the table.
• The number of times the query was recompiled from which to save.
• The query type and its dependency on the performance index and statistics.

In some cases, a stored procedure with temporary tables can be split into several smaller stored procedures for recompilation on smaller units.

In general, you should use table variables whenever possible, unless the amount of data is very large and you need to reuse the table. In this case, you can create indexes on the staging table to improve query performance. However, various scenarios may differ. Microsoft recommends that you do a test to verify that a table variable is more efficient than a temporary table for a particular query or stored procedure.

============================

Example One:

2. Declare @temp table
4. (
6. [id] int IDENTITY (1,1),
8. [Name] varchar (ten)
10. )
12. DECLARE @tempId int,@tempName varchar (ten)
16. INSERT INTO @temp values (' a ')
18. INSERT INTO @temp values (' B ')
20. INSERT INTO @temp values (' C ')
22. INSERT INTO @temp values (' d ')
24. INSERT INTO @temp values (' e ')
30. --select * from @temp
36. While EXISTS (select [id] from @temp)
38. Begin
40. Set ROWCOUNT 1 --equivalent Top (1)--specifically, loop nesting cannot use ' SET ROWCOUNT ', global variables
42. Select @tempId = [id],@tempName =[name] from @temp
44. SET ROWCOUNT 0
46. Delete from @temp where [id] = @tempId
50. Print ' Name:----' +@tempName
52. End

Example two:

DECLARE @procedureCode VARCHAR (8000)
DECLARE @applicationCode VARCHAR (50)
DECLARE @caseCode VARCHAR (10)

Set @procedureCode = ' '

SELECT @procedureCode +=procedurecode from Workflowprocedure

PRINT @procedureCode