By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

alter column fails due to statistics

P: n/a
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?

Dave
Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Metal Dave (me***@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
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

Jul 23 '05 #3

P: n/a
Metal Dave (me***@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.