SQL Server CE: Multiple single-column statistics connected by OR
source link: https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-ce-multiple-single-column-statistics-connected-by-or/ba-p/2728900?WT_mc_id=DOP-MVP-4025064
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SQL Server CE: Multiple single-column statistics connected by OR
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 are the selectivity of each value of the column in WHERE clause and P0>P1.
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.
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
IF exists(select 1 from sys.tables where name='SalesOrderDetail' and schema_id=schema_id('dbo'))
drop table SalesOrderDetail
select * into SalesOrderDetail from Sales.SalesOrderDetail
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)
The selectivity of SalesOrderID 44288 is 34/121317=0.00028025
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
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
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
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
Assessment:
T-SQL
use AdventureWorks2019
IF exists(select 1 from sys.tables where name='SalesOrderDetail' and schema_id=schema_id('dbo'))
drop table SalesOrderDetail
select * into SalesOrderDetail from Sales.SalesOrderDetail
--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)
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
%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-1631039357156.png%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308608i730F8EE028A3F978%2Fimage-size%2Fsmall%3Fv%3Dv2%26amp%3Bpx%3D200%22%20role%3D%22button%22%20title%3D%22Liwei_0-1631039357156.png%22%20alt%3D%22Liwei_0-1631039357156.png%22%20%2F%3E%3C%2FSPAN%3E%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
Sep 07 2021 11:33 AM
- Performance 123
Recommend
-
22
Head First Statistics This might come as a surprise to you. It’s not a book you can usually see in university courses — mostly because it’s full of visualizations and plain simple explanations....
-
4
When designing a table for a database, a column might need to be populated with a different number on every row inserted. An identity column might be a good way to automatically populate a numeric column each time a row is inserted. In this a...
-
9
Sort column by string in sql server? advertisements LogID Title Message 1 Error Occured Could not find stored procedure 'RT_SE...
-
3
Concatenate a single column in a comma-delimited list advertisements This question already has an answer here: Concatenate m...
-
6
SQL SERVER QUERY to get the Number of Distinct column in the table advertisements This is the sample expected result table As you can s...
-
1
Sorting a matrix by row or column statistics 1
-
3
n.2 - Sed challenge: join cal -y months into a single column date: 2021-11-16 This is a little sed challenge that ocurred to me somewhat recently, when I wondered how many weeks were between 2 dates. Of course, I just looked...
-
1
A single memory is stored across many connected brain regions Innovative brain-wide ma...
-
5
Connected logistics is an emerging technology that has the potential to revolutionize the supply chain industry. Connected logistics is a concept of using digital technologies, such as IoT and Big Data, to streamline and improve the efficienc...
-
5
Cube Formulas · Excel ·
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK