delete all statistics in db

Hi,
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.
Try the following post from Dan Guzman:
http://groups.google.com/group/micro...5ec3c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:

>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.
|||Thanks very much Sue.
"Sue Hoegemeier" wrote:

> Try the following post from Dan Guzman:
> http://groups.google.com/group/micro...5ec3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
>
>

delete all statistics in db

Hi,
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.Try the following post from Dan Guzman:
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/8dd9fd47a5ec3c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:
>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.|||Thanks very much Sue.
"Sue Hoegemeier" wrote:
> Try the following post from Dan Guzman:
> http://groups.google.com/group/microsoft.public.sqlserver.server/msg/8dd9fd47a5ec3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
> >Hi,
> >anybody have a script or an idea on how to delete all stats in a db?
> >while on the topic why do some stats (column I think) prevent column from
> >being upgraded via alter table statement? I would have expected stats to be
> >dropped as soon as column modified.
> >Thanks.
>

delete all statistics in db

Hi,
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.Try the following post from Dan Guzman:
http://groups.google.com/group/micr... />
c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:

>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.|||Thanks very much Sue.
"Sue Hoegemeier" wrote:

> Try the following post from Dan Guzman:
> http://groups.google.com/group/micr...>
c3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
>
>

default tables

hi all
is there any one who can tell the basic idea behind inserted, deleted mssql table. how these r affected on dml operations.
i m new to mssql.
thanksInserted and deleted are two special tables used in the trigger statements.

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

Check BOL for more information.

Harshal.