This might be dumb and feel like I am going back trying to understanding basics.
So I create a test table like below and create a clustered index on it
create table test( c1 int)
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 1
SET @Upper = 10000
while 1=1
begin
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
insert into test SELECT @Random
end
create clustered index cidx on test(c1)
Now Im running the below query with actual execution plan on
DECLARE @Min INT
SET @Min = 216 --selected this cause this was a histogram step
select * from test where c1 = @Min
select * from test where c1 = @Min option(recompile)
So for the first query the behavior is as expected, the estimated number of rows is calculated from density vector.
Seek Predicates - Seek Keys1: Prefix: [db].[dbo].[test].c1 = Scalar Operator([@Min])
But for the second query it looks like sql server can sniff the value with option(recompile). I thought SQL Server cannot sniff variables even if we use option recompile?
Seek Predicates - Seek Keys1: Prefix: [DB].[dbo].[test].c1 = Scalar Operator((216))
So as you can see from the estimated number of rows, the first one is 3.2511 which came from the density vector and for the second one the estimated number of rows of 7 comes from the histogram.
So is it true that SQL Server can sniff the variable when we recompile adhoc query or is it something that I do not understand?