Statistics in SQL Server - Microsoft SQL Server 2012 Performance Tuning - page 287

Getting ready

Generally, Auto_Create_Statistics is ON by default for databases, but let us make
sure that it remains OFF (for this exercise only) to check the effect of statistics on a non-key
column. Execute the following T-SQL script:

ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS OFF
GO

ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS OFF
GO

After making the proper settings at database level, let us now create one table that will be
used in demonstration of this recipe:

--creating one table "SaleOrdDemo" from
--Sales.SalesOrderHeader table of AdventureWorks2012
SELECT * into SalesOrdDemo FROM Sales.SalesOrderHeader
GO

How to do it...

Follow the steps provided here to perform this recipe:

1. Before we start anything, let us confirm that we have no statistics with us at the
moment, for the SalesOrdDemo table, with the help of the following script:
SELECT
object_id
,OBJECT_NAME(object_id) AS TableName
,name AS StatisticsName
,auto_created
FROM
sys.stats
where object_id=OBJECT_ID('SalesOrdDemo')
Order by object_id desc
GO

2. Now, create one clustered index on the SalesOrderID column of the

SalesOrdDemo table, which will create statistics for the SalesOrderID column:
CREATE Clustered Index idx_SalesOrdDemo_SalesOrderID
ON SalesOrdDemo(SalesOrderID)
GO