BlogmyQuery – BMQ

If you read the Books Online page describing the UPDATE STATISTICS command, you will see that there are some undocumented options.

UPDATE STATISTICS table | view [ { { index | statistics_name } | ( { index |statistics_name } [ ,...n ] ) } ] [    WITH [ [ FULLSCAN ] | SAMPLE number { PERCENT | ROWS } ] | RESAMPLE | <update_stats_stream_option> [ ,...n ] ] [ [ , ] [ ALL | COLUMNS | INDEX ] [ [ , ] NORECOMPUTE ] ] ; <update_stats_stream_option> ::= [ STATS_STREAM = stats_stream ] [ ROWCOUNT = numeric_constant ] [ PAGECOUNT = numeric contant ]

<update_stats_stream_option>

This syntax is for internal use only and is not supported. Microsoft reserves the right to change this syntax at any time.

There is a very good reason why these options are undocumented. They are meant for testing and debugging purposes, and should never ever be used on production systems.

Not everyone knows that query level hints (like loop join) will impact the entirety of a DML query plan. This includes foreign key validation and indexed view maintenance.

Let us look at an example with two tables involved in a foreign key constraint.

use tempdb

go

create table department(deptid int primary key clustered, deptname varchar(10))

go

create table employee(empid int primary key clustered, empname varchar(10), deptid int references department(deptid))

go

insert department values(1, 'Optimizer')

go

(continue reading...)

A question we are frequently asked is what happens when an update statement assigns a column to its same current value. For example,

use tempdb

go

create table t(i int, cc as i + 1)

create index t_cc on t(cc)

go

insert into t values(1)

go

update t set i = 1

go

update t set i = i

go