Difference between MSSQL temporary table and memory table

Difference 1: <PRE lang = SQL> Create Table # T (s varchar (128) Declare @ t table (s varchar (128 )) insert into # T select 'old value # 'insert into @ t select 'old value @ 'in in transaction update # t set S = 'new value # 'Update @ t set S =' new value @ 'rollback transactionselect * from # tselect * From @ ts --------------- old value # s --------------- new value @ </PRE> This line of code is clear at a glance, temporary tables can be used for transaction rollback, but table variables cannot. the author explains that the table variable is out of the scope of the transaction. Internal. therefore, even if the table variable encounters a rollback command, it does not actually perform rollback. difference 2: Any stored procedures containing temporary tables cannot be pre-compiled. in a long storage process, the advantage is even more prominent. difference 3: Table variables can only exist in the same range and cannot be cross-range. in addition, table variables are invisible in Built-in stored procedures or exec (string) statements, and cannot be used for insert/exec statements. the following example demonstrates how to compare the differences between temporary tables and table variables in pre-compilation during storage. create a test table: Create Table num (N int primary key, s varchar (128 )) goset nocount ondeclare @ n intset @ n = 1000000 while @ n> 0 begin insert into num select @ n, 'value: '+ convert (varchar, @ n) S Et @ n = @ n-1 endgo and then create the stored procedure: t1create procedure T1 @ total intas create table # T (N int, s varchar (128) insert into # T select N, s from num where n % 100> 0 and n <= @ total declare @ res varchar (128) Select @ res = max (s) from num where n <= @ total and not exists (select * from # t where # T. N = num. n) the greater the value of the parameter @ total for the go stored procedure, the longer the execution time. to accurately measure the execution time of a stored procedure, I use the following code: declare @ T1 datetime, @ n intset @ T1 = getdate () set @ n = 100 -(**) While @ n> 0 begin exec T1 1000-(*) set @ n = @ n-1 endselect datediff (MS, @ T1, getdate () Go (*) is the stored procedure parameter. now we will speed up this stored procedure for the first time: Add a primary key create procedure T2 @ total intas create table # T (N int primary key, s varchar (128 )) insert into # T select N, s from num where n % 100> 0 and n <= @ total declare @ res varchar (128) Select @ res = max (s) from num where n <= @ total and not exists (select * from # t where # T. N = num. n) You can To find that the speed has been greatly increased. next, we will increase the speed: Add a clustered index create procedure T3 @ total intas create table # T (N int, s varchar (128) insert into # T select N, s from num where n % 100> 0 and n <= @ total create clustered index tind on # T (n) Declare @ res varchar (128) Select @ res = max (s) from num where n <= @ total and not exists (select * from # t where # T. N = num. n) Go is surprised that the speed has improved a lot! Well, let's test the table variable speed. creare procedure V1 @ total INTAS declare @ V Table (N int, s varchar (128) insert into @ v select N, s from num where n % 100> 0 and n <= @ total declare @ res varchar (128) Select @ res = max (s) from num where n <= @ total and not exists (select * From @ V where v. N = num. n) go and then create a primary key: Create procedure V2 @ total INTAS declare @ V Table (N int primary key, s varchar (128) insert into @ v selec T n, s from num where n % 100> 0 and n <= @ total declare @ res varchar (128) Select @ res = max (s) from num where n <= @ total and not exists (select * From @ V v wherere v. N = num. n) Go <let's take a look at my test results! Table1, using SQL Server 2000, time in MS records T1 T2 T3 V1 V2 10 0.7 1 13.5 0.6 0.8 100 1.2 1.7 14.2 1.2 1.3 1000 7.1 27 7 5.5 5.3 72 57 82 71 48 10000 100000 883 480 580 840 510 1000000 45056 6090 15220 20240 12010 but the real shock is when you try the same on SQL Server 2005: table 2 N T1 T2 T3 V1 V2 10 0.5 0.5 5.3 0.2 0.2 2 100 1.2 6.4 61.8 2.5 1000 9.3 8.5 13.5 168 140 10000 17133 13910 100000 700 794 too long! Too long! 1000000 10556 8673 6440 too long! Too long! It is found that the speed of SQL2000 is much faster than that of 2005 in some cases! Conclusion: There are no general rules to guide you when to use temporary tables and when to use table variables. Be careful when porting complex logic stored procedures to sql2005! He may be several dozen times more efficient than 2000! In your actual test, test two extremes: sales data and excessive data.

-- Comments on temporary tables and Table variables can declare functions and variables as table types. Table variables can be used in functions, stored procedures, and batch processing. Try to use table variables instead of temporary tables. Table variables have the following advantages: the behavior of table variables is similar to that of local variables and have a clearly defined scope. This scope is the function, stored procedure, or batch processing that declares the variable. In its scope, table variables can be used as regular tables. This variable can be applied to tables or table expressions in select, insert, update, and delete statements. However, table cannot be used in the following statements: insert into table_variable exec stored procedure. Select select_list into table_variable statement. When the function, stored procedure, or batch processing of the table variable is defined, the table variable is automatically cleared. Using table variables in a stored procedure reduces the amount of recompilation in a stored procedure compared to using a temporary table. Transactions involving table variables only exist during table variable update. This reduces the need for table variables to lock and record resources. Table variables cannot be assigned values. In addition, because the table variable scope is limited and is not part of the persistent database, it is not affected by transaction rollback.