Filter Statistics

http://blogs.msdn.com/b/psssql/archive/2010/09/28/case-of-using-filtered-statistics.aspx

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.

http://sqlblog.com/blogs/elisabeth_redei/archive/2009/12/17/lies-damned-lies-and-statistics-part-iii-sql-server-2008.aspx

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

About these ads