In the world of SQL Server, statistics play a crucial role in query optimization. They provide the query optimizer with valuable information about the distribution of data in tables, enabling it to make informed decisions and generate efficient execution plans. While SQL Server automatically creates and updates statistics in most cases, there are scenarios where manually creating statistics without a corresponding index can be beneficial. In this article, we’ll explore those situations and provide practical T-SQL code examples to guide you through the process.

Scenario 1: Large Tables with Frequently Used Columns

One common scenario where manual statistics creation is advantageous is when you have a large table with a column that is frequently used in WHERE clauses but doesn’t warrant a dedicated index. By creating statistics on that column, you provide the query optimizer with valuable information about the data distribution, allowing it to estimate the selectivity of predicates more accurately. This, in turn, leads to better execution plans and improved query performance.

To manually create statistics without an index, you can use the CREATE STATISTICS statement. Here’s an example:

CREATE STATISTICS [Stats_CustomerName] 
ON [Sales].[Customers]([CustomerName]);

In this code snippet, we create statistics named “Stats_CustomerName” on the “CustomerName” column of the “Sales.Customers” table. By doing so, we provide the query optimizer with valuable information about the distribution of customer names, even though there is no corresponding index on that column.

Scenario 2: Tables with Filtered Indexes

Another scenario where manual statistics creation is beneficial is when you have a filtered index on a table. Filtered indexes only contain a subset of the data based on a specified condition. However, the query optimizer may not have statistics for the non-indexed portion of the data. By manually creating statistics on the non-indexed columns, you ensure that the query optimizer has a complete picture of the data distribution, leading to more accurate cardinality estimates and better query plans.

Here’s an example of creating statistics for a non-indexed column in a table with a filtered index:

CREATE STATISTICS [Stats_OrderDate] 
ON [Sales].[Orders] ([OrderDate]) 
WHERE [Status] = 'Completed';

In this case, we create statistics named “Stats_OrderDate” on the “OrderDate” column of the “Sales.Orders” table, specifically for rows where the “Status” column equals ‘Completed’. This provides the query optimizer with statistics tailored to the filtered portion of the data.

Maintaining Manual Statistics

It’s important to note that manually created statistics, like indexes, require maintenance. As the data in the table changes, the statistics can become stale, leading to suboptimal query plans. To ensure the statistics remain up to date, you can either rely on SQL Server’s automatic statistics update mechanism or manually update the statistics using the UPDATE STATISTICS statement.

Conclusion

In conclusion, manually creating statistics without a corresponding index can be a powerful technique to optimize query performance in SQL Server. By providing the query optimizer with valuable information about data distribution, even for non-indexed columns, you enable it to make more informed decisions and generate efficient execution plans. Remember to assess your specific scenarios and consider factors such as table size, query patterns, and maintenance requirements when deciding to manually create statistics.

Learn more: SQL Server Statistics