This actually helps SQL Server Query Optimizer to decide whether to use Index Seek or Index Scan.
You can also use below query, which uses the dynamic management function (sys.dm_db_stats_properties) to retrieve statistics properties with further details; for example, modification_counter, which shows the total number of modifications on the leading statistics column (the column on which the histogram is built) since the last statistics update.: Let’s start understanding this with an example.
Execute the below query to create a new database and set its AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS properties to OFF so that automatic statistics creation and updating does not happen on the tables of this database.
Now if you re-execute the above same queries, you will notice two things.
First, there is no warning for missing statistics and second “Actual Number of Rows” and “Estimated Number of Rows” are the same or very close to each other: In this article I discussed the basics of statistics and why it is needed.
The lowest of the sorted column values is the upper boundary value for the first histogram step.
Often columns being used in JOIN, WHERE, ORDER BY, or GROUP clauses are good candidate to have up-to-date statistics on them.
Now if your query uses Name and Salary without using Age, the density is not available for cardinality estimates.
Substantial data change operations (like insert, update, delete, or merge) change the data distribution in the table or indexed view and make the statistics goes stale or out-of-date, as it might not reflect the correct data distribution in a given column or index.
For example, based on these statistical information SQL Server Query Optimizer might decide whether to use the index seek operator or a more resource-intensive index scan operator in order to provide optimal query performance.