how do I alter the column with index on it

hi,
I have a sqlserver 2000 db. I want to alter two columns
from one of the tables to not allow nulls.
but these 2 columns (one if char, and the other is
smalldatetime), one have clustered index on it, and the
other one is part of a combined index of two columns.
is the only way for the column alteration is to drop the
index first, and then re-create it later?
many thanks!
JJ
Hi,
Altering the data type of one column which has participated in an index is
not supported. If the modified column participates in an index, the only
type changes that are allowed are increasing the length of a
variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing
nullability of the column, or both. I believe you have to drop the original
index and rebuild a new index.
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2131001c45a37$30e96600$a501280a@.phx.gbl...
> hi,
> I have a sqlserver 2000 db. I want to alter two columns
> from one of the tables to not allow nulls.
> but these 2 columns (one if char, and the other is
> smalldatetime), one have clustered index on it, and the
> other one is part of a combined index of two columns.
> is the only way for the column alteration is to drop the
> index first, and then re-create it later?
> many thanks!
> JJ
|||You can user table designer shipped with SQL server client tools or
Visuastudio to change the table.
Lishil, VSDATA Team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Subject: how do I alter the column with index on it
>Date: Thu, 24 Jun 2004 15:04:16 -0700
>Lines: 15
>Message-ID: <2131001c45a37$30e96600$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRaNzDpwp7EieNJRQCcEtOkif2wyg==
>Newsgroups:
microsoft.public.sqlserver.programming,microsoft.p ublic.sqlserver.tools
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.tools:24659
microsoft.public.sqlserver.programming:453163
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>hi,
>I have a sqlserver 2000 db. I want to alter two columns
>from one of the tables to not allow nulls.
>but these 2 columns (one if char, and the other is
>smalldatetime), one have clustered index on it, and the
>other one is part of a combined index of two columns.
>is the only way for the column alteration is to drop the
>index first, and then re-create it later?
>many thanks!
>JJ
>
|||thanks Lishil. I know the tool to alter it, I just want
to know whether I have to drop the index first before I
alter the column. please see my privious email for detail.
thanks.
JJ
>--Original Message--
>You can user table designer shipped with SQL server
client tools or
>Visuastudio to change the table.
>Lishil, VSDATA Team
>
>--
>microsoft.public.sqlserver.programming,microsoft. public.sq
lserver.tools[vbcol=seagreen]
microsoft.public.sqlserver.tools:24659
>microsoft.public.sqlserver.programming:453163
>.
>
|||thanks Hari. I fear so too. oh well...
thanks.
JJ
>--Original Message--
>Hi,
>Altering the data type of one column which has
participated in an index is
>not supported. If the modified column participates in an
index, the only
>type changes that are allowed are increasing the length
of a
>variable-length type (for example, VARCHAR(10) to VARCHAR
(20)), changing
>nullability of the column, or both. I believe you have to
drop the original
>index and rebuild a new index.
>
>--
>Thanks
>Hari
>MCDBA
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2131001c45a37$30e96600$a501280a@.phx.gbl...
>
>.
>
|||Ok, you already get answer from hari_prasad_k@.hotmail.com.
You do need to drop index in you case.
Lishi Liu, VSData team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>References: <2131001c45a37$30e96600$a501280a@.phx.gbl>
<$AXF2GuXEHA.2352@.cpmsftngxa06.phx.gbl>
>Subject: RE: how do I alter the column with index on it
>Date: Thu, 8 Jul 2004 18:24:14 -0700
>Lines: 61
>Message-ID: <2962a01c46553$72445bb0$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRlU3JEKPTFyjEUT129z6+ojcBcmg==
>Newsgroups:
microsoft.public.sqlserver.tools,microsoft.public. sqlserver.programming
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:456466
microsoft.public.sqlserver.tools:24467
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>thanks Lishil. I know the tool to alter it, I just want
>to know whether I have to drop the index first before I
>alter the column. please see my privious email for detail.
>thanks.
>JJ
>client tools or
>lserver.tools
>microsoft.public.sqlserver.tools:24659
>