1

I know that when we create an index then statistics are automatically created. My question is: Is it the only case when statistics are created? I remember that once I found on the internet that we have column statistics along with index statistics. Could you please enlighten me regarding this topic? When I execute the below script I get the following result:

use movies
go
EXEC sp_helpstats
@objname = '[dbo].[actors]',
@results = 'ALL';

This object does not have any statistics or indexes.

4

Column statistics are created automatically as needed by SQL Server when the AUTO_CREATE_STATISTICS database option is ON and also by an explict CREATE STATISTICS statement.

With the AUTO_CREATE_STATISTICS database option, the query optimizer will create single column stats during query compilation as needed on columns specified in query predicates when a stats histogram on the leading column doesn't already exist and that information is useful in plan selection (i.e. non-trivial plan).

Statistics may also be created beforehand using CREATE STATISTICS. Unlike auto-created statistics, CREATE STATISTICS allows one to create statistics on multiple columns to provide better estimates for compound predicates (e.g. FirstName = 'John' and LastName = 'Doh').

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.