How SQL server generates a Query plan with Auto Create Statistics set to OFF

Question

I am wondering how SQL server can generate a query plan with "Auto create statistics" set to "off".

I stumbled on the below context at the below link while searching for an answer(Sorry, I searched, but couldn't find anything better).

https://www.simple-talk.com/sql/performance/execution-plan-basics/

Table variables do not ever have statistics generated on them, so they are always assumed by the optimizer to have a single row, regardless of their actual size

Even though the above is applicable only for Table variables, I thought SQL Server will use the same principle to derive a query plan for a table and decided to do a small test.

I cleared the PROC cache and Ran the below query on a table with 1 million records

enter image description here

enter image description here

My test proved me wrong and SQL server reported missing statistics(as expected), But came up with the estimated number of rows as 31622(I expected it to be 1) when there are no statistics. How did the query optimizer come up with this value without statistics?

Answer 1

Table variables is another story, what the server uses when there are no statistics is estimation for unknown input:unknowns

So in your case the estimation will be select power(1000000, 0.75) = 31622 ( = predicate with non unique column prior to 2014)

Related book: T-SQL Querying by Itzik Ben-Gan