UPDATE STATISTICS undocumented options

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         […

12

Hints for DML queries

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,…

6

Non updating updates

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 =…

6