Selectivity and Estimated Row: Variable

 dans Sites Microsoft par

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(note,