22,206
社区成员
发帖
与我相关
我的任务
分享
create table tb(a int,b int,c int)
go
set nocount on
declare @i int,@j int;
set @i=1;
while (@i<=1000)
begin
set @j=1;
while @j<1000
begin
insert tb select @i,@j,0
set @j=@j+1
end
set @i=@i+1
end
go
set nocount off
select a,b,a+b as c from tb
--执行查询计划
--------------------------------------
SELECT 计算标量 表扫描
开销:0% 开销:3% 97%
alter table tb drop column c
alter table tb add c as a+b
select a,b,c from tb
--执行查询计划
--------------------------------------
SELECT 计算标量 计算标量 表扫描
开销:0% 开销:3% 开销:3% 94%
drop table tb
create table ta(A int,B int)
create view testAB
As
select A,B, A+B as C from ta
create table tb(A int,B int, C as A+B) --计算列
下面是这两个的执行计划
StmtText
-----------------------------
select * from tb --计算列
(1 行受影响)
StmtText
--------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([test1].[dbo].[tb].[C]=[test1].[dbo].[tb].[C]))
|--Compute Scalar(DEFINE:([test1].[dbo].[tb].[C]=[test1].[dbo].[tb].[A]+[test1].[dbo].[tb].[B]))
|--Table Scan(OBJECT:([test1].[dbo].[tb]))
(3 行受影响)
StmtText
------------------------------
select * from testAB --从视图中取
(1 行受影响)
StmtText
---------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1004]=[test1].[dbo].[ta].[A]+[test1].[dbo].[ta].[B]))
|--Table Scan(OBJECT:([test1].[dbo].[ta]))
(2 行受影响)
1:结果上没有什么区别
2:计算列中的列的计算时机在select 的时候
补充:从执行计划来看,视图和计算列都是在select 的时候计算;视图的效率好像高一点,而计算列多了一步Compute Scalar
create table #AA
(
A int,
B int
)
insert into #AA select 1,2
insert into #AA select 12,22
insert into #AA select 31,21
insert into #AA select 13,22
select A,B,A+B C from #AA
是先表扫描,然后计算