表变量和临时表

基本原则:能用表变量就用表变量.实在不行才使用临时表

表变量主要是开销系统的内存,而临时表则使用tempdb.对于小数据量的中间数据存储,可以使用表变量,而当需要临时保存的数据很大时,建议使用临时表。

用法:

--创建表变量

declare @tb table(id int,namevarchar(50),age int)

insert @tb select 1,'nn',14 select * from @tb

--创建临时表

create table #t(id int,namevarchar(50),years int,numsint) 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 (

idint,

namenvarchar(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:

-- Create date: <2012-07-04>

-- Description: <获取家庭吸烟总量(创建表变量)> -- =============================================

ALTER PROCEDURE [dbo].[Family_SmokeTotal] (@Family_IDint) AS

declare @tab table(Person_IDint) insert into @tab(Person_ID)

selectPerson_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 rq

drop table #tmp