Sladescross's Blog

Blogging about Sharepoint related stuff

Filter Statistics May 17, 2012

Filed under: Filter Statistics,SQL Tip,Statistics — sladescross @ 11:35 am

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 = 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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 63 other followers