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).
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
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?