SQL Server 2017 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select 1 A, 1 B, 1 C into DEMO union all select 1 A, 1 B , 1 C;
2 rows affected

insert into DEMO select 1,1,1 from DEMO a, DEMO b, DEMO c, DEMO d, DEMO e, DEMO f, DEMO g, DEMO h;
256 rows affected

insert into DEMO values (2,0,0);
1 rows affected

insert into DEMO select a.A, a.B, a.C from DEMO a;
259 rows affected

create index DEMO_AB on DEMO(A,B);
select A,count(*) from DEMO group by A;


A
(No column name)


1
516

2
2


set statistics profile on
select sum(A) from DEMO where A=2;


(No column name)


4




Rows
Executes
StmtText
StmtId
NodeId
Parent
PhysicalOp
LogicalOp
Argument
DefinedValues
EstimateRows
EstimateIO
EstimateCPU
AvgRowSize
TotalSubtreeCost
OutputList
Warnings
Type
Parallel
EstimateExecutions


1
1
SELECT SUM([A]) FROM [DEMO] WHERE [A]=@1
1
1
0
null
null
null
null
1
null
null
null
0.0032859
null
null
SELECT
False
null

0
0
  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))
1
2
1
Compute Scalar
Compute Scalar
DEFINE:([Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END)
[Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END
1
0
0
11
0.0032859
[Expr1003]
null
PLAN_ROW
False
1

1
1
       |--Stream Aggregate(DEFINE:([Expr1005]=Count(*), [Expr1006]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A])))
1
3
2
Stream Aggregate
Aggregate
null
[Expr1005]=Count(*), [Expr1006]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A])
1
0
1.7E-06
11
0.0032859
[Expr1005], [Expr1006]
null
PLAN_ROW
False
1

2
1
            |--Index Seek(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
1
4
3
Index Seek
Index Seek
OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD
[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]
2
0.003125
0.0001592
11
0.0032842
[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]
null
PLAN_ROW
False
1


select sum(B) from DEMO where A=2;


(No column name)


0




Rows
Executes
StmtText
StmtId
NodeId
Parent
PhysicalOp
LogicalOp
Argument
DefinedValues
EstimateRows
EstimateIO
EstimateCPU
AvgRowSize
TotalSubtreeCost
OutputList
Warnings
Type
Parallel
EstimateExecutions


1
1
SELECT SUM([B]) FROM [DEMO] WHERE [A]=@1
1
1
0
null
null
null
null
1
null
null
null
0.0032859
null
null
SELECT
False
null

0
0
  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))
1
2
1
Compute Scalar
Compute Scalar
DEFINE:([Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END)
[Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END
1
0
0
11
0.0032859
[Expr1003]
null
PLAN_ROW
False
1

1
1
       |--Stream Aggregate(DEFINE:([Expr1005]=Count(*), [Expr1006]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[B])))
1
3
2
Stream Aggregate
Aggregate
null
[Expr1005]=Count(*), [Expr1006]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[B])
1
0
1.7E-06
11
0.0032859
[Expr1005], [Expr1006]
null
PLAN_ROW
False
1

2
1
            |--Index Seek(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
1
4
3
Index Seek
Index Seek
OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD
[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[B]
2
0.003125
0.0001592
11
0.0032842
[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[B]
null
PLAN_ROW
False
1


select sum(C) from DEMO where A=2;


(No column name)


0




Rows
Executes
StmtText
StmtId
NodeId
Parent
PhysicalOp
LogicalOp
Argument
DefinedValues
EstimateRows
EstimateIO
EstimateCPU
AvgRowSize
TotalSubtreeCost
OutputList
Warnings
Type
Parallel
EstimateExecutions


1
1
SELECT SUM([C]) FROM [DEMO] WHERE [A]=@1
1
1
0
null
null
null
null
1
null
null
null
0.005583622
null
null
SELECT
False
null

0
0
  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))
1
2
1
Compute Scalar
Compute Scalar
DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END)
[Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END
1
0
0
11
0.005583622
[Expr1003]
null
PLAN_ROW
False
1

1
1
       |--Stream Aggregate(DEFINE:([Expr1009]=Count(*), [Expr1010]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C])))
1
3
2
Stream Aggregate
Aggregate
null
[Expr1009]=Count(*), [Expr1010]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C])
1
0
1.7E-06
11
0.005583622
[Expr1009], [Expr1010]
null
PLAN_ROW
False
1

2
1
            |--Table Scan(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO]), WHERE:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=(2)))
1
4
3
Table Scan
Table Scan
OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO]), WHERE:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=(2))
[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]
2
0.004606482
0.0007268
15
0.005333282
[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]
null
PLAN_ROW
False
1


select sum(C) from DEMO where A=2 option (table hint(DEMO, index (DEMO_AB)));


(No column name)


0




Rows
Executes
StmtText
StmtId
NodeId
Parent
PhysicalOp
LogicalOp
Argument
DefinedValues
EstimateRows
EstimateIO
EstimateCPU
AvgRowSize
TotalSubtreeCost
OutputList
Warnings
Type
Parallel
EstimateExecutions


1
1
select sum(C) from DEMO where A=2 option (table hint(DEMO, index (DEMO_AB)))
1
1
0
null
null
null
null
1
null
null
null
0.007882357
null
null
SELECT
False
null

0
0
  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))
1
2
1
Compute Scalar
Compute Scalar
DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END)
[Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END
1
0
0
11
0.007882357
[Expr1003]
null
PLAN_ROW
False
1

1
1
       |--Stream Aggregate(DEFINE:([Expr1009]=Count(*), [Expr1010]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C])))
1
3
2
Stream Aggregate
Aggregate
null
[Expr1009]=Count(*), [Expr1010]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C])
1
0
1.7E-06
11
0.007882357
[Expr1009], [Expr1010]
null
PLAN_ROW
False
1

2
1
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
1
4
3
Nested Loops
Inner Join
OUTER REFERENCES:([Bmk1000])
null
2
0
8.36E-06
11
0.007880656
[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]
null
PLAN_ROW
False
1

2
1
                 |--Index Seek(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=(2)) ORDERED FORWARD)
1
5
4
Index Seek
Index Seek
OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=(2)) ORDERED FORWARD, FORCEDINDEX
[Bmk1000]
2
0.003125
0.0001592
15
0.0032842
[Bmk1000]
null
PLAN_ROW
False
1

2
2
                 |--RID Lookup(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
1
7
4
RID Lookup
RID Lookup
OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD
[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]
1
0.003125
0.0001581
11
0.004588096
[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]
null
PLAN_ROW
False
2

Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
1	1	SELECT SUM([A]) FROM [DEMO] WHERE [A]=@1	1	1	0	null	null	null	null	1	null	null	null	0.0032859	null	null	SELECT	False	null
0	0	\|--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))	1	2	1	Compute Scalar	Compute Scalar	DEFINE:([Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END)	[Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END	1	0	0	11	0.0032859	[Expr1003]	null	PLAN_ROW	False	1
1	1	\|--Stream Aggregate(DEFINE:([Expr1005]=Count(*), [Expr1006]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A])))	1	3	2	Stream Aggregate	Aggregate	null	[Expr1005]=Count(*), [Expr1006]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A])	1	0	1.7E-06	11	0.0032859	[Expr1005], [Expr1006]	null	PLAN_ROW	False	1
2	1	\|--Index Seek(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)	1	4	3	Index Seek	Index Seek	OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD	[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]	2	0.003125	0.0001592	11	0.0032842	[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]	null	PLAN_ROW	False	1

Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
1	1	SELECT SUM([B]) FROM [DEMO] WHERE [A]=@1	1	1	0	null	null	null	null	1	null	null	null	0.0032859	null	null	SELECT	False	null
0	0	\|--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))	1	2	1	Compute Scalar	Compute Scalar	DEFINE:([Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END)	[Expr1003]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END	1	0	0	11	0.0032859	[Expr1003]	null	PLAN_ROW	False	1
1	1	\|--Stream Aggregate(DEFINE:([Expr1005]=Count(*), [Expr1006]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[B])))	1	3	2	Stream Aggregate	Aggregate	null	[Expr1005]=Count(*), [Expr1006]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[B])	1	0	1.7E-06	11	0.0032859	[Expr1005], [Expr1006]	null	PLAN_ROW	False	1
2	1	\|--Index Seek(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)	1	4	3	Index Seek	Index Seek	OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD	[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[B]	2	0.003125	0.0001592	11	0.0032842	[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[B]	null	PLAN_ROW	False	1

Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
1	1	SELECT SUM([C]) FROM [DEMO] WHERE [A]=@1	1	1	0	null	null	null	null	1	null	null	null	0.005583622	null	null	SELECT	False	null
0	0	\|--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))	1	2	1	Compute Scalar	Compute Scalar	DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END)	[Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END	1	0	0	11	0.005583622	[Expr1003]	null	PLAN_ROW	False	1
1	1	\|--Stream Aggregate(DEFINE:([Expr1009]=Count(*), [Expr1010]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C])))	1	3	2	Stream Aggregate	Aggregate	null	[Expr1009]=Count(*), [Expr1010]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C])	1	0	1.7E-06	11	0.005583622	[Expr1009], [Expr1010]	null	PLAN_ROW	False	1
2	1	\|--Table Scan(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO]), WHERE:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=(2)))	1	4	3	Table Scan	Table Scan	OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO]), WHERE:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=(2))	[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]	2	0.004606482	0.0007268	15	0.005333282	[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]	null	PLAN_ROW	False	1

Rows	Executes	StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
1	1	select sum(C) from DEMO where A=2 option (table hint(DEMO, index (DEMO_AB)))	1	1	0	null	null	null	null	1	null	null	null	0.007882357	null	null	SELECT	False	null
0	0	\|--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))	1	2	1	Compute Scalar	Compute Scalar	DEFINE:([Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END)	[Expr1003]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END	1	0	0	11	0.007882357	[Expr1003]	null	PLAN_ROW	False	1
1	1	\|--Stream Aggregate(DEFINE:([Expr1009]=Count(*), [Expr1010]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C])))	1	3	2	Stream Aggregate	Aggregate	null	[Expr1009]=Count(*), [Expr1010]=SUM([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C])	1	0	1.7E-06	11	0.007882357	[Expr1009], [Expr1010]	null	PLAN_ROW	False	1
2	1	\|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))	1	4	3	Nested Loops	Inner Join	OUTER REFERENCES:([Bmk1000])	null	2	0	8.36E-06	11	0.007880656	[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]	null	PLAN_ROW	False	1
2	1	\|--Index Seek(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=(2)) ORDERED FORWARD)	1	5	4	Index Seek	Index Seek	OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[DEMO_AB]), SEEK:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[A]=(2)) ORDERED FORWARD, FORCEDINDEX	[Bmk1000]	2	0.003125	0.0001592	15	0.0032842	[Bmk1000]	null	PLAN_ROW	False	1
2	2	\|--RID Lookup(OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)	1	7	4	RID Lookup	RID Lookup	OBJECT:([fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD	[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]	1	0.003125	0.0001581	11	0.004588096	[fiddle_e71450c764104fbf97cbedd34da0dc5a].[dbo].[DEMO].[C]	null	PLAN_ROW	False	2