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----------------------------------