Alter table errors due to statistics

I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
GaryRun:
drop statistics hind_61_3
and then do your ALTER TABLE.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>> Run:
>> drop statistics hind_61_3
>> and then do your ALTER TABLE.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
>> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
>> I'm attempting to change a column data type from int to nvarchar(16) on a
>> production database. When executing:
>> alter table x alter column y nvarchar(16)
>> I get the error:
>> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
>> this
>> column
>>
>> I would be forever grateful if someone could tell me how to get around
>> this
>> issue.
>> Thanks in advance,
>> Gary
>>
>

Alter table errors due to statistics

I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
Run:
drop statistics hind_61_3
and then do your ALTER TABLE.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>
sql

alter column fails due to statistics

Hello,

A script we run against the database as part of the upgrade of our product
is failing with the following message:

ALTER TABLE ALTER COLUMN EncodedID failed because STATISTICS hind_61_3
accesses this column

The line that fails is:

alter table Badge alter column EncodedID nvarchar(16)

It's clear that there's some kind of automatically generated statistics
object referencing the column that prevents us from changing it from an
int to an nvarchar. However, I have no idea how that got there - my best
guess would be that it has something to do with the auto generate
statistics option being set on the database. However that seems odd to me
because we've done lots and lots of work like this and not encountered the
problem. It also seems like a quick fix would be to perform:

Drop Statistics Badge.encodedid

However I am afraid subsequent statements might fail on this db since I
wasn't really expecting this in the first place. Does anyone have any
insight?

DaveMetal Dave (metal@.spam.spam) writes:
> A script we run against the database as part of the upgrade of our product
> is failing with the following message:
> ALTER TABLE ALTER COLUMN EncodedID failed because STATISTICS hind_61_3
> accesses this column
> The line that fails is:
> alter table Badge alter column EncodedID nvarchar(16)
> It's clear that there's some kind of automatically generated statistics
> object referencing the column that prevents us from changing it from an
> int to an nvarchar. However, I have no idea how that got there - my best
> guess would be that it has something to do with the auto generate
> statistics option being set on the database. However that seems odd to me
> because we've done lots and lots of work like this and not encountered the
> problem.

I played around a little, and not all table changes caused complaints
about statistics. But changing a column from varchar to nvarchar did.

The statistics in question is not a regular auto-statistics, their
names are different. My guess is that this could be something created
by the Index Tuning Wizard. "hind" makes me think of "hypothetical indexes".

>It also seems like a quick fix would be to perform:
> Drop Statistics Badge.encodedid

Well, "DROP STATISTICS Badge.hind_61_3" would be better.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 20 Dec 2004, Erland Sommarskog wrote:

> The statistics in question is not a regular auto-statistics, their
> names are different. My guess is that this could be something created
> by the Index Tuning Wizard. "hind" makes me think of "hypothetical indexes".

This makes a lot of sense. Thanks for the input. It also explains how this
might have gotten onto a customers database, if their dba started poking
around without us knowing about it.

Do you happen to know the naming scheme of the auto stats?

> >It also seems like a quick fix would be to perform:
> > Drop Statistics Badge.encodedid
> Well, "DROP STATISTICS Badge.hind_61_3" would be better.

Oops, that's what I intended to type. Thanks for the correction. I don't
even think mine would have run.

Dave|||Metal Dave (metal@.spam.spam) writes:
> Do you happen to know the naming scheme of the auto stats?

The ones I have seen are like _WA_Sys_columnname_1AD3FDA4.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp