Here is the statement “create statistics Region_stats_id on Region (id) where name = ‘Dallas’”.
Now if you execute the same select statement (select detail from Region join Sales on Region.id = Sales.id where name=’Dallas’), the cardinality estimate is correct as shown below for the nested loop join.
To start with, I need to know my domain of values:
SELECT MIN(c2) AS minVal, AVG(c2) avgVal, MAX(c2) maxVal FROM test1
I have only some 1 200 000 rows in this table, so I am going to go ahead and try with 3 ranges (ideally you should have an idea how many rows are in each range):
CREATE STATISTICS testStatLow ON test1 (c2) WHERE c2 < 200 GO
CREATE STATISTICS testStatMed ON test1 (c2) WHERE c2 > 200 AND c2 < 400 GO
CREATE STATISTICS testStatHi ON test1 (c2) WHERE c2 > 400 GO