SQL Server CE: Multiple single-column statistics connected by OR

Published Sep 07 2021 07:49 AM 1,102 Views
Microsoft

In post SQL Server CE: Multiple single-column statistics, I discussed the selectivity when AND is used to join the columns in Where clause. Today, I'm going to talk about the OR operator

 

Let's say we have four predicates connected by OR with selectivites P0,P1,P2 and P3,

 

Legacy CE

 The combined selectivity is : 1-(1-P0)*(1-P1)*(1-P2)*(1-P3).

If there are only two predicates, the formula is relative simple: (P0+P1)-(P0*P1)

 

New CE

1.The combined selectivity is : 1-(1-P0)*(1-P1)^(1/2)*(1-P2)^(1/4)*(1-P3)^(1/8)

2.P0,P1,P1 and P2 are the selectivity of each value of the column in WHERE clause and  P0>P1>P2>P3.

3.We can have up to 4 predicates. If there are more than 4 columns in the where clause, it only counts the first 4, the rest of them are ignored.

Combined selectivity of four predicates: 1-(1-P0)*(1-P1)^(1/2)*(1-P2)^(1/4)*(1-P3)^(1/8), where   P0>P1>P2>P3.

In practice, Please use following formula to calcualte the selectivity if the combined selectivity has more than 2 predicate.

1-

Liwei_0-1631025801463.png

 

 

As you expected, more columns used in where clause, more estiamted rows you get.

 

 

Here are examples , adventure 2019 OLTP  database is used in this example

use AdventureWorks2019

go

IF exists(select 1 from sys.tables where name='SalesOrderDetail' and schema_id=schema_id('dbo'))

drop table SalesOrderDetail

Go

select * into SalesOrderDetail  from Sales.SalesOrderDetail

GO

create statistics I_ProductID on SalesOrderDetail(ProductID) with fullscan

create statistics I_SalesOrderID on SalesOrderDetail(SalesOrderID) with fullscan

 

 

Here is the T-SQL query I'm going to test

select * from SalesOrderDetail where ProductID=708 or SalesOrderID=44288

 

The selectivity of ProductId 708 is 3007/121317=0.02478630

dbcc show_statistics(SalesOrderDetail,I_ProductID)

Liwei_1-1631025801464.png

 

 

The selectivity of SalesOrderID 44288 is 34/121317=0.00028025

Liwei_2-1631025801465.png

 

 

 

Legacy CE:

Combined selectivity:(P0+P1)-(P0*P1)=(0.02478630+0.00028025)-(0.02478630*0.00028025)=0.0250596036394250

Estimated row: 0.0250596036394250*121317=3040.15, is rounded down to 3040

select * from SalesOrderDetail where ProductID=708 or SalesOrderID=44288 option(recompile,querytraceon 9481)--trace flag 9481 is used to force legacy CE

Liwei_3-1631025801466.png

 

 

 

 

New CE:

Combined selectivity:1-(1-P0)*sqrt(1-P1)=1-(1-0.02478630)*(sqrt(1-0.00028025)=0.0249229613952225

Estimated row: 0.0249229613952225*121317=3023.57 is rounded up to 3024

select * from SalesOrderDetail where ProductID=708 or SalesOrderID=44288 option(recompile,querytraceon 2312)--2312 is used to force New CE

Liwei_4-1631025801466.png

 

 

Related trace flags

Trace flag 9471: Pickup the lowest selectivity(Most rows) from all predicates, and ignore rest of them. This trace flag only works when New CE is used.

Combined selectivity: Max(P0,P1,P2,P3,P4..)

In this case, it's Max(P0,P1)*CARD=MAX(0.02478630,0.00028025)*CARD=0.02478630*CARD=0.02478630*121317=3007

select * from SalesOrderDetail where ProductID=708 or SalesOrderID=44288 option(recompile,querytraceon 2312,querytraceon 9471)---2312 is used to force New CE

Liwei_5-1631025801466.png

 

 

Trace flag 9472:Switch to independent formula, the result is as same as the result of Legacy CE and this trace flag only works when New CE is used.

select * from SalesOrderDetail where ProductID=708 or SalesOrderID=44288 option(recompile,querytraceon 2312,querytraceon 9472)--force New CE

Liwei_6-1631025801467.png

 

 

Assessment:

T-SQL

use AdventureWorks2019

go

IF exists(select 1 from sys.tables where name='SalesOrderDetail' and schema_id=schema_id('dbo'))

drop table SalesOrderDetail

go

select * into SalesOrderDetail  from Sales.SalesOrderDetail

GO

--create five statistics

create statistics I_ProductID on SalesOrderDetail(ProductID) with fullscan

create statistics I_orderqty on SalesOrderDetail(orderqty) with fullscan

create statistics I_SalesOrderID on SalesOrderDetail(SalesOrderID) with fullscan

create statistics I_SpecialOfferid on SalesOrderDetail(SpecialOfferid) with fullscan

create statistics I_modifiedDate on SalesOrderDetail(modifiedDate) with fullscan

 

 

1.Please calculate the estimated rows of following queries and run the queries to verify

select *From SalesOrderDetail where ProductId=708 or orderqty=10 or modifiedDate='2011-05-31 00:00:00.000' or SpecialOfferid=4  or SalesOrderID=44288 option(recompile,querytraceon 9481)

go

select *From SalesOrderDetail where ProductId=708 or orderqty=10 or modifiedDate='2011-05-31 00:00:00.000' or SpecialOfferid=4  or SalesOrderID=44288 option(recompile,querytraceon 2312)

2.If there are multi-column stats, does it make different?

%3CLINGO-SUB%20id%3D%22lingo-sub-2728900%22%20slang%3D%22en-US%22%3ESQL%20Server%20CE%3A%20Multiple%20single-column%20statistics%20connected%20by%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2728900%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20post%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-support%2Fsql-server-ce-multiple-single-column-statistics%2Fba-p%2F2726969%22%20target%3D%22_self%22%3ESQL%20Server%20CE%3A%20Multiple%20single-column%20statistics%3C%2FA%3E%2C%26nbsp%3BI%20discussed%20the%20selectivity%20when%20%3CSTRONG%3EAND%3C%2FSTRONG%3E%20is%20used%20to%20join%20the%20columns%20in%20Where%20clause.%20Today%2C%20I'm%20going%20to%20talk%20about%20the%20OR%20operator%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20we%20have%20four%20predicates%20connected%20by%20OR%20with%20selectivites%20P0%2CP1%2CP2%20and%20P3%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ELegacy%20CE%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BThe%20combined%20selectivity%20is%20%3A%201-(1-P0)*(1-P1)*(1-P2)*(1-P3).%3C%2FP%3E%0A%3CP%3EIf%20there%20are%20only%20two%20predicates%2C%20the%20formula%20is%20relative%20simple%3A%20(P0%2BP1)-(P0*P1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ENew%20CE%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E1.The%20combined%20selectivity%20is%20%3A%201-(1-P0)*(1-P1)%5E(1%2F2)*(1-P2)%5E(1%2F4)*(1-P3)%5E(1%2F8)%3C%2FP%3E%0A%3CP%3E2.P0%2CP1%20are%20the%20selectivity%20of%20each%20value%20of%20the%20column%20in%20WHERE%20clause%20and%26nbsp%3B%20P0%26gt%3BP1.%3C%2FP%3E%0A%3CP%3E3.We%20can%20have%20up%20to%204%20predicates.%20If%20there%20are%20more%20than%204%20columns%20in%20the%20where%20clause%2C%20it%20only%20counts%20the%20first%204%2C%20the%20rest%20of%20them%20are%20ignored.%3C%2FP%3E%0A%3CP%3ECombined%20selectivity%20of%20four%20predicates%3A%201-(1-P0)*(1-P1)%5E(1%2F2)*(1-P2)%5E(1%2F4)*(1-P3)%5E(1%2F8)%2C%20where%26nbsp%3B%26nbsp%3B%20P0%26gt%3BP1%26gt%3BP2%26gt%3BP3.%3C%2FP%3E%0A%3CP%3EIn%20practice%2C%20Please%20use%20following%20formula%20to%20calcualte%20the%20selectivity%20if%20the%20combined%20selectivity%20has%20more%20than%202%20predicate.%3C%2FP%3E%0A%3CP%3E1-%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_0-1631025801463.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308547i2897F65201F2E815%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Liwei_0-1631025801463.png%22%20alt%3D%22Liwei_0-1631025801463.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20you%20expected%2C%20more%20columns%20used%20in%20where%20clause%2C%20more%20estiamted%20rows%20you%20get.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20are%20examples%20%2C%20adventure%202019%20OLTP%26nbsp%3B%20database%20is%20used%20in%20this%20example%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Euse%20AdventureWorks2019%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3EIF%20exists(select%201%20from%20sys.tables%20where%20name%3D'SalesOrderDetail'%20and%20schema_id%3Dschema_id('dbo'))%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Edrop%20table%20SalesOrderDetail%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3EGo%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Eselect%20*%20into%20SalesOrderDetail%26nbsp%3B%20from%20Sales.SalesOrderDetail%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3EGO%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ecreate%20statistics%20I_ProductID%20on%20SalesOrderDetail(ProductID)%20with%20fullscan%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ecreate%20statistics%20I_SalesOrderID%20on%20SalesOrderDetail(SalesOrderID)%20with%20fullscan%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20T-SQL%20query%20I'm%20going%20to%20test%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Eselect%20*%20from%20SalesOrderDetail%20where%20ProductID%3D708%20or%20SalesOrderID%3D44288%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20selectivity%20of%20ProductId%20708%20is%203007%2F121317%3D0.02478630%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Edbcc%20show_statistics(SalesOrderDetail%2CI_ProductID)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_1-1631025801464.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308548i358EA7C306B96E09%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_1-1631025801464.png%22%20alt%3D%22Liwei_1-1631025801464.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20selectivity%20of%20SalesOrderID%2044288%20is%2034%2F121317%3D0.00028025%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_2-1631025801465.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308549i993EA2E3EF6D2A9C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_2-1631025801465.png%22%20alt%3D%22Liwei_2-1631025801465.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ELegacy%20CE%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ECombined%20selectivity%3A(P0%2BP1)-(P0*P1)%3D(0.02478630%2B0.00028025)-(0.02478630*0.00028025)%3D0.0250596036394250%3C%2FP%3E%0A%3CP%3EEstimated%20row%3A%200.0250596036394250*121317%3D3040.15%2C%20is%20rounded%20down%20to%203040%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Eselect%20*%20from%20SalesOrderDetail%20where%20ProductID%3D708%20or%20SalesOrderID%3D44288%20option(recompile%2Cquerytraceon%209481)-%3C%2FFONT%3E-trace%20flag%209481%20is%20used%20to%20force%20legacy%20CE%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_3-1631025801466.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308550i4B79F5738B60B2AD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_3-1631025801466.png%22%20alt%3D%22Liwei_3-1631025801466.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ENew%20CE%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ECombined%20selectivity%3A1-(1-P0)*sqrt(1-P1)%3D1-(1-0.02478630)*(sqrt(1-0.00028025)%3D0.0249229613952225%3C%2FP%3E%0A%3CP%3EEstimated%20row%3A%200.0249229613952225*121317%3D3023.57%20is%20rounded%20up%20to%203024%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Eselect%20*%20from%20SalesOrderDetail%20where%20ProductID%3D708%20or%20SalesOrderID%3D44288%20option(recompile%2Cquerytraceon%202312)%3C%2FFONT%3E--2312%20is%20used%20to%20force%20New%20CE%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_4-1631025801466.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308552i39EFBF9E065C9A9B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_4-1631025801466.png%22%20alt%3D%22Liwei_4-1631025801466.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ERelated%20trace%20flags%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ETrace%20flag%209471%3C%2FSTRONG%3E%3A%20Pickup%20the%20lowest%20selectivity(Most%20rows)%20from%20all%20predicates%2C%20and%20ignore%20rest%20of%20them.%20This%20trace%20flag%20only%20works%20when%20New%20CE%20is%20used.%3C%2FP%3E%0A%3CP%3ECombined%20selectivity%3A%20Max(P0%2CP1%2CP2%2CP3%2CP4..)%3C%2FP%3E%0A%3CP%3EIn%20this%20case%2C%20it's%20Max(P0%2CP1)*CARD%3DMAX(0.02478630%2C0.00028025)*CARD%3D0.02478630*CARD%3D0.02478630*121317%3D3007%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Eselect%20*%20from%20SalesOrderDetail%20where%20ProductID%3D708%20or%20SalesOrderID%3D44288%20option(recompile%2Cquerytraceon%202312%2Cquerytraceon%209471)%3C%2FFONT%3E---2312%20is%20used%20to%20force%20New%20CE%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_5-1631025801466.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308551iAD16700DCEB38B65%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_5-1631025801466.png%22%20alt%3D%22Liwei_5-1631025801466.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ETrace%20flag%209472%3C%2FSTRONG%3E%3ASwitch%20to%20independent%20formula%2C%20the%20result%20is%20as%20same%20as%20the%20result%20of%20Legacy%20CE%20and%20this%20trace%20flag%20only%20works%20when%20New%20CE%20is%20used.%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Eselect%20*%20from%20SalesOrderDetail%20where%20ProductID%3D708%20or%20SalesOrderID%3D44288%20option(recompile%2Cquerytraceon%202312%2Cquerytraceon%209472)%3C%2FFONT%3E--force%20New%20CE%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_6-1631025801467.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308553iCCA21625DF0140FD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_6-1631025801467.png%22%20alt%3D%22Liwei_6-1631025801467.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EAssessment%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ET-SQL%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Euse%20AdventureWorks2019%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3EIF%20exists(select%201%20from%20sys.tables%20where%20name%3D'SalesOrderDetail'%20and%20schema_id%3Dschema_id('dbo'))%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Edrop%20table%20SalesOrderDetail%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Eselect%20*%20into%20SalesOrderDetail%26nbsp%3B%20from%20Sales.SalesOrderDetail%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3EGO%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3E--create%20five%20statistics%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ecreate%20statistics%20I_ProductID%20on%20SalesOrderDetail(ProductID)%20with%20fullscan%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ecreate%20statistics%20I_orderqty%20on%20SalesOrderDetail(orderqty)%20with%20fullscan%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ecreate%20statistics%20I_SalesOrderID%20on%20SalesOrderDetail(SalesOrderID)%20with%20fullscan%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ecreate%20statistics%20I_SpecialOfferid%20on%20SalesOrderDetail(SpecialOfferid)%20with%20fullscan%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ecreate%20statistics%20I_modifiedDate%20on%20SalesOrderDetail(modifiedDate)%20with%20fullscan%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E1.Please%20calculate%20the%20estimated%20rows%20of%20following%20queries%20and%20run%20the%20queries%20to%20verify%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Eselect%20*From%20SalesOrderDetail%20where%20ProductId%3D708%20or%20orderqty%3D10%20or%20modifiedDate%3D'2011-05-31%2000%3A00%3A00.000'%20or%20SpecialOfferid%3D4%26nbsp%3B%20or%20SalesOrderID%3D44288%20option(recompile%2Cquerytraceon%209481)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Ego%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%233366FF%22%3Eselect%20*From%20SalesOrderDetail%20where%20ProductId%3D708%20or%20orderqty%3D10%20or%20modifiedDate%3D'2011-05-31%2000%3A00%3A00.000'%20or%20SpecialOfferid%3D4%26nbsp%3B%20or%20SalesOrderID%3D44288%20option(recompile%2Cquerytraceon%202312)%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E2.If%20there%20are%20multi-column%20stats%2C%20does%20it%20make%20different%3F%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2728900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_0-1631039627186.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308613iA1E5D019254A43B0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Liwei_0-1631039627186.png%22%20alt%3D%22Liwei_0-1631039627186.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2728900%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Sep 13 2021 03:27 PM
Updated by: