表变量与临时表的优缺点_Gordennizaicunzai的博客-CSDN博客

DECLARE @t TABLE(i int) ----定义表变量@t

SELECT *FROM @t        -----访问OK

insert into @t select 1 -----插入数据OK

select * from  @t      -------访问OK
go                     -------结束批处理
select * from @t       -------不在作用域出错

if 'a'='b'
begin
DECLARE @t TABLE(i int)
end
SELECT *FROM @t        -----仍然可以访问！

create table #t (i int)
declare @t table(i int)
BEGIN TRAN ttt
insert into #t select 1
insert into @t select 1
SELECT * FROM #t  ------returns 1 rows
SELECT * FROM @t  ------returns 1 rows
ROLLBACK tran ttt
SELECT * FROM #t    -------no rows
SELECT * FROM @t    -------still 1 rows
drop table #t       ----no use drop @t in session

DECLARE @t TABLE 
(
col1 int index inx_1 CLUSTERED, 
col2 int  index index_2 NONCLUSTERED,
       index index_3 NONCLUSTERED(col1,col2)
)

CREATE FUNCTION TVP_Customers (@cust nvarchar(10))
RETURNS TABLE
AS
 RETURN
 (SELECT RowNum, CustomerID, OrderDate, ShipCountry
 FROM BigOrders
 WHERE CustomerID = @cust);
GO
CREATE FUNCTION TVF_Customers (@cust nvarchar(10))
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,
 ShipCountry nvarchar(30))
AS
BEGIN
 INSERT INTO @T
  SELECT RowNum, CustomerID, OrderDate, ShipCountry
  FROM BigOrders
  WHERE CustomerID = @cust
  RETURN
END;

DBCC FREEPROCCACHE
GO
SELECT * FROM TVF_Customers('CENTC');
GO
SELECT * FROM TVP_Customers('CENTC');
GO
SELECT * FROM TVF_Customers('SAVEA');
GO
SELECT * FROM TVP_Customers('SAVEA');
GO

select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) b
where b.text like '%_Customers%'

declare @t table (i int)
select * from @t-----此时0行预估行数为1行
insert into @t select 1
select * from @t-----此时1行,预估行数仍为1行
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
select * from @t ----此时19行,预估行数仍为1行

--....无论实际@t中有多少行,由于没有重编译,预估均为1行

declare @t table (i int)
select * from @t option(recompile)-----此时0行预估行数为1行
insert into @t select 1
select * from @t  option(recompile)-----此时1行,预估行数为1行
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
select * from @t  option(recompile)----此时19行,预估行数为19行
--....当加入重编译hint时,优化器就知道的表变量的行数.

declare @i int
set @i=13
DECLARE @T TABLE(I INT);
INSERT INTO @T VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
------表变量中存在个数字
select * from @T where I < 1  option(recompile) ------20*30% 预估数为6
select * from @T where I > @i option(recompile) --------20*30%预估数为6
select * from @T where I like @i  option(recompile) --------20*10% 预估数为2
select * from @T where I like 1  option(recompile)  --------20*10 预估数为2
select * from @T where I = @i  option(recompile) --------POWER(20.00000,0.75) 预估数为9.45742
select * from @T where I = 1  option(recompile)  --------POWER(20.00000,0.75) 预估数为9.45742

insert into @T
select DatabaseLogID from AdventureWorks2008R2.dbo.DatabaseLog------insert new records
select * from @T option(recompile) ------------此时数据为行
select * from @T where I = 1  option(recompile)--------------------POWER(1617.00000,0.75) 预估数为254.99550

----SSMS测试数据
Use tempdb
create table t
( id int identity,str1 char(8000))----more pages for many records

insert into t select 'a'
go 100

----sqlquerystress
select * into #t
from t----57s

----sqlquerystress
declare @t table
( id int,str1 char(8000))
insert into @t
select * from t-----1s