Rowgoal impacts the estimated row


The engineer asked me a question, how does the optimizer estimate the row for index scan(range scan). he observed 100-109 estimated row for index scan but he wants to know the reason.

 

 

As my understanding, if there is no predicated, the estimated row should equal total of index row count. Then I requested the plan and test query.

 

Here is my test script

create table ta(c1 int ,c2 int, c3 varchar(1000))

go

create table tb(c1 int ,c2 int, c3 varchar(1000))

go

 

with cte

as

(

select ROW_NUMBER() over(order by o.object_id) id from sys.columns o

)