Tuesday, March 27, 2012

Altering column with index

Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?You'll need to drop the indexes/constraints and recreate after changing the
collation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||Drop the indexes (and any constraints) first, then alter the column and
restore the indexes (and constraints).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?|||Whell Tom
It seems to be very agly to do that.
When i do it with the enterprise Manager i don't do all this stuff
Are you sure i need to do all of that?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Drop the indexes (and any constraints) first, then alter the column and
> restore the indexes (and constraints).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||
Roy Goldhammer wrote:

>Whell Tom
>It seems to be very agly to do that.
>When i do it with the enterprise Manager i don't do all this stuff
>
Enterprise Manager probably does it for you behind the scenes. While
EM doesn't do everything in the best way, you could generate the change
script from EM and use it as a template for creating your own script. If
an index exists on a varchar column, and then the collation is changed,
the index must be rebuilt. There's no way around it, since a change in
collation may change the ordering of the values, which is what the
index is there to implement.
Steve Kass
Drew University

>Are you sure i need to do all of that?
>"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>
>

No comments:

Post a Comment