SQL Server: How to create User Defined Filtered Statistics?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In the previous articles, we have seen basic about SQL Statistics and Automated configuration of Statistics.
In this post, I am going share script to create user defined Statistics in SQL Server.

The query optimizer already generates the necessary statistics for a high-quality query plan and in a few cases, you need to create additional statistics for better performance.

You can also use the Database Engine Tuning Advisor which suggests you to create additional statistics or not.
For the Database performance tuning and optimization, User defined Database Statistics are playing one of the important Role.

For example, we can create Filtered Statistics on a column like Filtered Index.
We can also create user defined normal Statistics but Filtered Statistics is a one kind of special tips for performance optimization.

Now take one example that, Table has 1000000 records and Statistics of this table is also updated and Query Optimizer will choose best suitable execution plane base on 1000000 records.

But query contains additional WHERE condition to filter out a data and after applying this filter, there are only 1000 desired records.

So here, Execution plan should be generated for 1000 desired records instead of 1000000 records.

Syntax to Create Filtered Statistics:

Basically, in the Filtered Statistics, you can add WHERE condition for desired statistics only. This is one the advanced technique of performance tuning.