在存儲過程中﹐下面兩種方法﹐哪種效率高且數據庫服務器開銷小?
方法一﹕
CREATE procedure SP_Test
......
declare TABLE [#Warehouse_Product_JxcTmp] (
[日期] [smalldatetime] NULL ,
[憑單編號] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[廠內料號] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[廠外料號] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[期初數量] [int] NULL default (0) ,
[收入數量] [int] NULL default (0) ,
[發出數量] [int] NULL default (0),
[結存數量] AS ([期初數量] + [收入數量] - [發出數量]) ,
[賬存數量] [int] NULL
)
insert into #Warehouse_Product_JxcTmp(日期,憑單編號,廠內料號,廠外料號,期初數量)
select ...
...
truncate table #Warehouse_Product_JxcTmp
drop table #Warehouse_Product_JxcTmp 方法二﹕
CREATE procedure SP_Test
......
declare @Warehouse_Product_JxcTmp TABLE (
[日期] [smalldatetime] NULL ,
[憑單編號] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[廠內料號] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[廠外料號] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[期初數量] [int] NULL default (0) ,
[收入數量] [int] NULL default (0) ,
[發出數量] [int] NULL default (0),
[結存數量] AS ([期初數量] + [收入數量] - [發出數量]) ,
[賬存數量] [int] NULL
)
insert into @Warehouse_Product_JxcTmp(日期,憑單編號,廠內料號,廠外料號,期初數量)
select ...
...是不是要看這個表的訪問數據量大小來確定啊?
如果訪問不是很頻繁且數據量小的話﹐就用表變量的方法(即方法二)﹔
如果訪問很頻繁且數據量大的話﹐就用臨時表的方法(即方法一)嗎?
方法一﹕
CREATE procedure SP_Test
......
declare TABLE [#Warehouse_Product_JxcTmp] (
[日期] [smalldatetime] NULL ,
[憑單編號] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[廠內料號] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[廠外料號] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[期初數量] [int] NULL default (0) ,
[收入數量] [int] NULL default (0) ,
[發出數量] [int] NULL default (0),
[結存數量] AS ([期初數量] + [收入數量] - [發出數量]) ,
[賬存數量] [int] NULL
)
insert into #Warehouse_Product_JxcTmp(日期,憑單編號,廠內料號,廠外料號,期初數量)
select ...
...
truncate table #Warehouse_Product_JxcTmp
drop table #Warehouse_Product_JxcTmp 方法二﹕
CREATE procedure SP_Test
......
declare @Warehouse_Product_JxcTmp TABLE (
[日期] [smalldatetime] NULL ,
[憑單編號] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[廠內料號] [varchar] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[廠外料號] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[期初數量] [int] NULL default (0) ,
[收入數量] [int] NULL default (0) ,
[發出數量] [int] NULL default (0),
[結存數量] AS ([期初數量] + [收入數量] - [發出數量]) ,
[賬存數量] [int] NULL
)
insert into @Warehouse_Product_JxcTmp(日期,憑單編號,廠內料號,廠外料號,期初數量)
select ...
...是不是要看這個表的訪問數據量大小來確定啊?
如果訪問不是很頻繁且數據量小的話﹐就用表變量的方法(即方法二)﹔
如果訪問很頻繁且數據量大的話﹐就用臨時表的方法(即方法一)嗎?
以前讨论过的。能否指明是哪個貼子啊。