找到答案了基本原则:能用表变量就用表变量.实在不行才使用临时表 表变量主要是开销系统的内存,而临时表则使用tempdb.对于小数据量的中间数据存储,可以使用表变量,而当需要临时保存的数据很大时,建议使用临时表. declare @tb table(id int,name varchar(50),age int) --创建表变量insert @tb select 1,'nn',14 select * from @tb create table #t(id int,name varchar(50),years int,nums int)--创建临时表insert #t select 1,'nn',14,15 union all select 1,'nn',14,15 insert into #t exec sp_gets --可以用于存储过程或动态SQL结合select * from #t drop table #t --删除临时表 实例 ------------------------------------------------------------------ ----------------------------declare @tab table ( id int, name nvarchar(50) ) insert into @tab(id,name) select person_id,1 from personinfo select * from @tab ------------------------------------------------------------------ ---------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Jack zhang> -- Create date: <2007-08-04> -- Description: <获取家庭吸烟总量(创建表变量)> -- =============================================ALTER PROCEDURE [dbo].[Family_SmokeTotal] (@Family_ID int) AS declare @tab table(Person_ID int) insert into @tab(Person_ID) select Person_ID from PersonInfo where Family_ID=@Family_ID select Sum(SmokeCount) '家庭吸烟总数' from PersonActionInfo where Person_id in (select Person_id from @tab)//临时表 CREATE TABLE #tmp ( rq NVARCHAR(10), shengfu NVARCHAR(1) ) INSERT into #tmp select'2005-05-09','胜' Insert into #tmp select'2005-05-09','胜' insert into #tmp select'2005-05-09','负' insert into #tmp select'2005-05-09','负' insert into #tmp select'2005-05-10','胜' insert into #tmp select'2005-05-10','负' insert into #tmp select'2005-05-10','负'SELECT rq,sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rqdrop table #tmp
##table为全局变量Declare @tb table,这个嘛,有人说用临时表的效率快点,偶也弄不明白
##table为全局变量
Declare @tb table 创建一个变量表
表变量主要是开销系统的内存,而临时表则使用tempdb.对于小数据量的中间数据存储,可以使用表变量,而当需要临时保存的数据很大时,建议使用临时表.
declare @tb table(id int,name varchar(50),age int) --创建表变量insert @tb select 1,'nn',14
select * from @tb
create table #t(id int,name varchar(50),years int,nums int)--创建临时表insert #t select 1,'nn',14,15
union all select 1,'nn',14,15
insert into #t exec sp_gets --可以用于存储过程或动态SQL结合select * from #t
drop table #t --删除临时表
实例
------------------------------------------------------------------ ----------------------------declare @tab table
(
id int,
name nvarchar(50)
)
insert into @tab(id,name)
select person_id,1 from personinfo
select * from @tab
------------------------------------------------------------------ ----------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Jack zhang>
-- Create date: <2007-08-04>
-- Description: <获取家庭吸烟总量(创建表变量)>
-- =============================================ALTER PROCEDURE [dbo].[Family_SmokeTotal]
(@Family_ID int)
AS
declare @tab table(Person_ID int)
insert into @tab(Person_ID)
select Person_ID from PersonInfo where Family_ID=@Family_ID
select Sum(SmokeCount) '家庭吸烟总数' from PersonActionInfo where Person_id in (select Person_id from @tab)//临时表
CREATE TABLE #tmp
(
rq NVARCHAR(10),
shengfu NVARCHAR(1)
)
INSERT into #tmp select'2005-05-09','胜'
Insert into #tmp select'2005-05-09','胜'
insert into #tmp select'2005-05-09','负'
insert into #tmp select'2005-05-09','负'
insert into #tmp select'2005-05-10','胜'
insert into #tmp select'2005-05-10','负'
insert into #tmp select'2005-05-10','负'SELECT rq,sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负'
from
#tmp
group by rqdrop table #tmp