Archive for July, 2006

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

(continue reading...)

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, 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...)


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

go

update t set i = i

go

(continue reading...)

  •   
  • Copyright © 1996-2010 BlogmyQuery - BMQ. All rights reserved.
    iDream theme by Templates Next | Powered by WordPress