sp_modifystats

This stored procedure should be included in the Reference Manual Volume 3: Procedures.

Description

Allows the System Administrator to modify the density values of a column—or columns—in sysstatistics

Syntax

sp_modifystats [database].[owner].table_name,
{“column_group” | “all”},
MODIFY_DENSITY,
{range | total},
{absolute | factor},
“value”

Or,

sp_modifystats [database].[owner].table_name, 
column_name,
REMOVE_SKEW_FROM_DENSITY

Parameters

table_name

is the name of the table to change. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.

column_group

an ordered list of column names. To change a statistic for multiple columns (such as a density value), list the columns in the order used to create the statistic. Separate the column names with commas. For example, if your table has a density statistic on columns a1, a2, a3, a4:

all

modifies all column group for this table. Because “all” is a keyword, it requires quotes.

MODIFY_DENSITY

allows you to modify either the range or total density of a column or column group to the granularity specified in the value parameter. Range cell density represents the average number of duplicates of all values that are represented by range cells in a histogram. See the Performance and Tuning Guide for more information.

range

modifies the range cell density.

total

modifies the total cell density.

absolute

ignore the current value and use the number specified by the value parameter.

factor

multiply the current statistical value by the value parameter.

value

is either the specified density value or a multiple for the current density. Must be between zero and one, inclusive, if absolute is specified.

column_name

is the name of a column in that table.

REMOVE_SKEW_FROM_DENSITY

allows the System Administrator to change the total density of a column to be equal to the range density which is useful when data skew is present. Total density represents the average number of duplicates for all values, those in both frequency and range cells. Total density is used to estimate the number of matching rows for joins and for search arguments whose value is not known when the query is optimized. See the Performance and Tuning Guide for more information.

REMOVE_SKEW_FROM_DENSITY also updates the total density of any composite column statistics for which this column is the leading attribute. Most commonly, a composite index for which this column is the leading attribute would produce these composite column statistics, but they can also be produced when you issue a composite update statistics command.

Examples

Example 3

Example 1

Changes the range density for column group c00, c01 in table tab_1 to 0.50000000:

sp_modifystats  "tab_1", "c00, c01", MODIFY_DENSITY, range, absolute, "0.5"

Example 4

Example 2

The total density for column group c00, c01 in tab_1 is multiplied by .5. That is, divided in half:

sp_modifystats  "tab_1", "c00,c01", MODIFY_DENSITY, total, factor, "0.5"

Example 5

Example 3

The total density for all the columns in table tab_1 is multiplied by .5.

sp_modifystats  "tab_1", "all", MODIFY_DENSITY, total, factor, "0.5"

Example 6

Example 4

Total density for all column groups starting with c12 is changed to equal the range density.

sp_modifystats "tab_1", "c12" REMOVE_SKEW_FROM_DENSITY

Usage

Permissions

Only a System Administrator can execute sp_modifystats.

See also

Command

update statistics

System table

sysstatistics