Selectivity and Estimated Row: Variable

SQL Server does not sniff for variable, it just simply uses the fixed value.

 

I'm going to use AdventureWorks 2019 in this post.

--------------------Please run this script---------------

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 iProductID ON SalesOrderDetail(productid) with fullscan

go

dbcc traceon(3604,2363)---trace flag 2363 displays more detail about the selectivity

go

--------------------Please run this script---------------

 

 

Equality(=): all density

DECLARE @pid INT = 0

SELECT * FROM SalesOrderDetail WHERE ProductID = @pid

 

 

456='All density'*card=0.003759399*121317

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

----------------------------------trace flag 2363 output----------------------------------

Begin selectivity computation

Input tree:

  LogOp_Select

      CStCollBaseTable(ID=1, CARD=121317 TBL: Sales.SalesOrderDetail)

      ScaOp_Comp x_cmpEq

          ScaOp_Identifier QCOL: [AdventureWorks2019].[Sales].[SalesOrderDetail].ProductID

          ScaOp_Identifier COL: @pid

Plan for computation:

  CSelCalcHistogramComparison(POINT PREDICATE)

Loaded histogram for column QCOL: [AdventureWorks2019].[Sales].[SalesOrderDetail].ProductID from stats with id 3

Selectivity: 0.0037594

Stats collection generated:

  CStCollFilter(ID=2, CARD=456.079)

      CStCollBaseTable(ID=1, CARD=121317 TBL: Sales.SalesOrderDetail)

End selectivity computation

----------------------------------trace flag 2363 output----------------------------------

 

 

Non-Equality(<>): 0.9

 

DECLARE @pid INT = 0

SELECT * FROM SalesOrderDetail WHERE ProductID <> @pid

 

 121317*0.9=109185.3, is rounded down to 109185

 

----------------------------------trace flag 2363 output----------------------------------

Begin selectivity computation

Input tree:

  LogOp_Select

      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)

      ScaOp_Comp x_cmpNe

          ScaOp_Identifier QCOL: [AdventureWorks2019].[dbo].[SalesOrderDetail].ProductI

          ScaOp_Identifier COL: @productid

Plan for computation:

  CSelCalcFixedFilter (0.9)

Selectivity: 0.9

Stats collection generated:

  CStCollFilter(ID=2, CARD=109185)

      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)

End selectivity computation

----------------------------------trace flag 2363 output----------------------------------

 

 

 

Inequality(>,>=,<,<=):0.3

declare @productid int=0

select *From SalesOrderDetail where ProductID>@productid

 

121317*0.3=36395.1, is around down to 36395

Please try >=,< and <=, they all use the same selectivity.

----------------------------------trace flag 2363 output----------------------------------

Begin selectivity computation

Input tree:

  LogOp_Select

      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)

      ScaOp_Comp x_cmpGt

          ScaOp_Identifier QCOL: [AdventureWorks2019].[dbo].[SalesOrderDetail].ProductID

          ScaOp_Identifier COL: @productid

Plan for computation:

  CSelCalcFixedFilter (0.3)

Selectivity: 0.3

Stats collection generated:

  CStCollFilter(ID=2, CARD=36395.1)

      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)

End selectivity computation

 

----------------------------------trace flag 2363 output----------------------------------