пишу след запрос
X++:
select Closed, count(*) from inventsum
group by Closed
вот результат
Closed
----------- -----------
0 396231
1 923596
просто при использовании некластерного индекса в планах появляется еще и Bookmark Lookup
вот два плана. .с использованием индекса и нет
X++:
1 1 |--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017])) 46 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017]) [Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017] 1.0 0.0 1.6147925E-6 41 9.701564E-3 [Expr1002], [Expr1003] NULL PLAN_ROW 0 1.0
1 1 |--Stream Aggregate(DEFINE:([Expr1015]=Count(*), [Expr1016]=SUM([A].[POSTEDQTY]), [Expr1017]=SUM([A].[POSTEDVALUE]))) 46 3 2 Stream Aggregate Aggregate NULL [Expr1015]=Count(*), [Expr1016]=SUM([A].[POSTEDQTY]), [Expr1017]=SUM([A].[POSTEDVALUE]) 1.0 0.0 1.6147925E-6 41 9.701564E-3 [Expr1015], [Expr1016], [Expr1017] NULL PLAN_ROW 0 1.0
22 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[INVENTDIMID]) WITH PREFETCH) 46 4 3 Nested Loops Inner Join OUTER REFERENCES:([A].[INVENTDIMID]) WITH PREFETCH NULL 16.147924 0.0 6.7498324E-5 337 9.6999491E-3 [A].[POSTEDQTY], [A].[POSTEDVALUE] NULL PLAN_ROW 0 1.0
22 1 |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([domoNew].[dbo].[INVENTSUM] AS [A])) 46 6 4 Bookmark Lookup Bookmark Lookup BOOKMARK:([Bmk1000]), OBJECT:([domoNew].[dbo].[INVENTSUM] AS [A]) [A].[INVENTDIMID], [A].[POSTEDQTY], [A].[POSTEDVALUE] 16.147924 7.8122527E-4 1.7762717E-5 234 4.098813E-3 [A].[INVENTDIMID], [A].[POSTEDQTY], [A].[POSTEDVALUE] NULL PLAN_ROW 0 1.0
22 1 | |--Index Seek(OBJECT:([domoNew].[dbo].[INVENTSUM].[I_174CLOSEDITEMDIMIDX] AS [A]), SEEK:([A].[DATAAREAID]='dat' AND [A].[CLOSED]=0 AND [A].[ITEMID]='0000005') ORDERED FORWARD) 46 8 6 Index Seek Index Seek OBJECT:([domoNew].[dbo].[INVENTSUM].[I_174CLOSEDITEMDIMIDX] AS [A]), SEEK:([A].[DATAAREAID]='dat' AND [A].[CLOSED]=0 AND [A].[ITEMID]='0000005') ORDERED FORWARD, FORCEDINDEX [Bmk1000] 16.147924 3.2034011E-3 9.6424199E-5 78 3.2998251E-3 [Bmk1000] NULL PLAN_ROW 0 1.0
22 22 |--Clustered Index Seek(OBJECT:([domoNew].[dbo].[INVENTDIM].[I_698DIMIDIDX] AS [B]), SEEK:([B].[DATAAREAID]='dat' AND [B].[INVENTDIMID]=[A].[INVENTDIMID]) ORDERED FORWARD) 46 9 4 Clustered Index Seek Clustered Index Seek OBJECT:([domoNew].[dbo].[INVENTDIM].[I_698DIMIDIDX] AS [B]), SEEK:([B].[DATAAREAID]='dat' AND [B].[INVENTDIMID]=[A].[INVENTDIMID]) ORDERED FORWARD, FORCEDINDEX NULL 1.0 3.2034011E-3 7.9607002E-5 111 5.5336375E-3 NULL NULL PLAN_ROW 0 16.147924
1 1 |--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017])) 49 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017]) [Expr1002]=If ([Expr1015]=0) then NULL else [Expr1016], [Expr1003]=If ([Expr1015]=0) then NULL else [Expr1017] 1.0 0.0 1.6147925E-6 41 1.1219452E-2 [Expr1002], [Expr1003] NULL PLAN_ROW 0 1.0
1 1 |--Stream Aggregate(DEFINE:([Expr1015]=Count(*), [Expr1016]=SUM([A].[POSTEDQTY]), [Expr1017]=SUM([A].[POSTEDVALUE]))) 49 3 2 Stream Aggregate Aggregate NULL [Expr1015]=Count(*), [Expr1016]=SUM([A].[POSTEDQTY]), [Expr1017]=SUM([A].[POSTEDVALUE]) 1.0 0.0 1.6147925E-6 41 1.1219452E-2 [Expr1015], [Expr1016], [Expr1017] NULL PLAN_ROW 0 1.0
22 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[INVENTDIMID]) WITH PREFETCH) 49 4 3 Nested Loops Inner Join OUTER REFERENCES:([A].[INVENTDIMID]) WITH PREFETCH NULL 16.147924 0.0 6.7498324E-5 337 1.1217837E-2 [A].[POSTEDQTY], [A].[POSTEDVALUE] NULL PLAN_ROW 0 1.0
22 1 |--Clustered Index Seek(OBJECT:([domoNew].[dbo].[INVENTSUM].[I_174ITEMDIMIDX] AS [A]), SEEK:([A].[DATAAREAID]='dat' AND [A].[ITEMID]='0000005'), WHERE:([A].[CLOSED]=0) ORDERED FORWARD) 49 6 4 Clustered Index Seek Clustered Index Seek OBJECT:([domoNew].[dbo].[INVENTSUM].[I_174ITEMDIMIDX] AS [A]), SEEK:([A].[DATAAREAID]='dat' AND [A].[ITEMID]='0000005'), WHERE:([A].[CLOSED]=0) ORDERED FORWARD, FORCEDINDEX [A].[INVENTDIMID], [A].[CLOSED], [A].[POSTEDQTY], [A].[POSTEDVALUE] 16.147924 2.7128116E-3 7.8514153E-5 234 5.5826516E-3 [A].[INVENTDIMID], [A].[CLOSED], [A].[POSTEDQTY], [A].[POSTEDVALUE] NULL PLAN_ROW 0 1.0
22 22 |--Clustered Index Seek(OBJECT:([domoNew].[dbo].[INVENTDIM].[I_698DIMIDIDX] AS [B]), SEEK:([B].[DATAAREAID]='dat' AND [B].[INVENTDIMID]=[A].[INVENTDIMID]) ORDERED FORWARD) 49 7 4 Clustered Index Seek Clustered Index Seek OBJECT:([domoNew].[dbo].[INVENTDIM].[I_698DIMIDIDX] AS [B]), SEEK:([B].[DATAAREAID]='dat' AND [B].[INVENTDIMID]=[A].[INVENTDIMID]) ORDERED FORWARD, FORCEDINDEX NULL 1.0 3.2034011E-3 7.9607002E-5 111 5.5336375E-3 NULL NULL PLAN_ROW 0 16.147924