What is the effect of statistics on queries in SQL Server

Brief introduction

SQL Server Query Analyzer is cost-based. Typically, the Query Analyzer determines how to choose an efficient query route based on predicates, such as which index to choose. And every time the Query Analyzer looking for the path, and not every time to statistics the number of rows included in the index, the range of values, but based on certain conditions to create and update the information after the save to the database, which is called statistical information.

How to view statistics

Viewing SQL Server statistics is simple, using the following directives:

DBCC show_statistics (' table name ', ' Index name ')

The resulting results are shown in Figure 1.

Figure 1. Statistical information

How statistical information affects queries

Let's use a simple example to see how the statistics affect the Query Analyzer. I set up a test table with two int values of columns, where ID is self increment, nonclustered index is set up on ref, insert 100 data, from 1 to 100, and then insert 9,900 data equal to 100. The statistics in Figure 1 are statistical information for the sample data.

At this point, where I use the ref value as the query condition, but given the different values, we can see that the Query Analyzer has made a different choice based on the statistics, as shown in Figure 2.

Figure 2. According to different predicates, the query optimizer makes different choices