X hits on this document

# A Problem-Solution Approach - page 26 / 48

54 views

0 shares

26 / 48

660

### CHAPTER 28 ■ PERFORMANCE TUNING

C R E A T E S T A T I S T I C S S t a t s _ C u s t o m e r _ C u s t o m e r T y p ON Sales.Customer (CustomerType) WITH FULLSCAN e

# How It Works

This recipe demonstrated manually creating statistics on the Sales.Customer table. The first line of code designated the statistics name:

## CREATE STATISTICS Stats_Customer_CustomerType

The second line of code designated the table to create statistics on, followed by the column name used to generate the statistics:

## ON Sales.Customer (CustomerType)

The last line of code designated that all rows in the table would be read in order to generate the statistics:

# Updating Statistics

After you create statistics, if you wish to manually update statistics, you can use the UPDATE STATISTICS command. The syntax is as follows:

UPDATE STATISTICS table | view [ {

{ index | statistics_name } | ( { i n d e x | s t a t i s t i c s _ n a m e } [

,

...n ] )

}

] [

## WITH

[

[ | | ] [[, [[ ,

FULLSCAN ] SAMPLE number { PERCENT | ROWS } ] RESAMPLE

] [ ALL | COLUMNS | INDEX ] ] NORECOMPUTE ]

] The arguments of this command are described in Table 28-2.

## Table 28-2. UPDATE STATISTICS Arguments

Argument table | view

{ index | ( {index| [ ,...n ]

statistics_name}| statistics_name} )}

FULLSCAN| { PERCENT

SAMPLE number | ROWS } |RESAMPLE

Description The table name or indexed view for which to update statistics.

The name of the index or named statistics to update.

FULLSCAN, when specified, reads all rows when generating the statistics. SAMPLE reads either a defined number of rows or a percentage. RESAMPLE updates statistics based on the original sampling method.

 Document views 54 Page views 54 Page last viewed Sat Oct 22 16:33:46 UTC 2016 Pages 48 Paragraphs 987 Words 15822