Newbie: HELP takes too long for ALTER TABLE!

13 May 2005 5:19 PM

steve

Hi,
I have a table with 16 million lines. After reading about data types and
importing it from Access i decided to change some things to save space and
make the querries more efficient.

I entered the following in SQL Query Analyzer:

USE rafalebd
ALTER TABLE [tblMeasMetHor] ALTER COLUMN [METEOid] [char] (8) NOT NULL
ALTER TABLE [tblMeasMetHor] ALTER COLUMN [date_time] [smalldatetime] NOT
NULL
ALTER TABLE [tblMeasMetHor] ALTER COLUMN [ParamMeteo] [char] (3) NOT NULL
ALTER TABLE [tblMeasMetHor] ALTER COLUMN [Valide] [char] (1) NOT NULL
ALTER TABLE [tblMeasMetHor] ALTER COLUMN [val] [real] NOT NULL
ALTER TABLE [tblMeasMetHor] ALTER COLUMN [f] [char] (2) NULL
--ALTER TABLE [tblMeasMetHor] ADD CONSTRAINT pk_MeasMetHor PRIMARY KEY
(METEOid, date_time, ParamMeteo, Valide)
GO

I commented out the PK entry because it gives me an error that one of the
fields is NOT null (original). Why? Isn't it supposed to apply the changes
line by line in order?

I let it run for 34 minutes!!! and nothing yet. I stoppped it.
Is this normal ?????

Your help would be appreciated.

13 May 2005 5:43 PM

JT

Alter the table using one single ALTER TABLE.. statement. Also, drop all
indexes, constraints, and triggers before performing the alter and re-apply
them after, so the table is re-indexed only once. This will at least make it
run faster.

Show quote

"steve" <st***@here.com> wrote in message
news:3l5he.81285$L31.712419@wagner.videotron.net...
> Hi,
> I have a table with 16 million lines. After reading about data types and
> importing it from Access i decided to change some things to save space and
> make the querries more efficient.
>
> I entered the following in SQL Query Analyzer:
>
> USE rafalebd
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [METEOid] [char] (8) NOT NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [date_time] [smalldatetime] NOT
> NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [ParamMeteo] [char] (3) NOT
NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [Valide] [char] (1) NOT NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [val] [real] NOT NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [f] [char] (2) NULL
> --ALTER TABLE [tblMeasMetHor] ADD CONSTRAINT pk_MeasMetHor PRIMARY KEY
> (METEOid, date_time, ParamMeteo, Valide)
> GO
>
> I commented out the PK entry because it gives me an error that one of the
> fields is NOT null (original). Why? Isn't it supposed to apply the changes
> line by line in order?
>
> I let it run for 34 minutes!!! and nothing yet. I stoppped it.
> Is this normal ?????
>
> Your help would be appreciated.
>
>

13 May 2005 5:58 PM

steve

Thanx but
As i confirmed on the net , ALTER COLUMN cannot be done in series within ONE
Alter Table.
Other s, such as constraints they can. You can verify that by looking at the
definition.

Also, there are no views, constrains, or indexes in my DB.

I am very perplexed.

"JT" <some***@microsoft.com> a écrit dans le message de news:

uekWTO%23VFHA.2***@TK2MSFTNGP14.phx.gbl...
Show quote

>
> Alter the table using one single ALTER TABLE.. statement. Also, drop all
> indexes, constraints, and triggers before performing the alter and
> re-apply
> them after, so the table is re-indexed only once. This will at least make
> it
> run faster.
>
> "steve" <st***@here.com> wrote in message
> news:3l5he.81285$L31.712419@wagner.videotron.net...
>> Hi,
>> I have a table with 16 million lines. After reading about data types and
>> importing it from Access i decided to change some things to save space
>> and
>> make the querries more efficient.
>>
>> I entered the following in SQL Query Analyzer:
>>
>> USE rafalebd
>> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [METEOid] [char] (8) NOT NULL
>> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [date_time] [smalldatetime] NOT
>> NULL
>> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [ParamMeteo] [char] (3) NOT
> NULL
>> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [Valide] [char] (1) NOT NULL
>> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [val] [real] NOT NULL
>> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [f] [char] (2) NULL
>> --ALTER TABLE [tblMeasMetHor] ADD CONSTRAINT pk_MeasMetHor PRIMARY KEY
>> (METEOid, date_time, ParamMeteo, Valide)
>> GO
>>
>> I commented out the PK entry because it gives me an error that one of the
>> fields is NOT null (original). Why? Isn't it supposed to apply the
>> changes
>> line by line in order?
>>
>> I let it run for 34 minutes!!! and nothing yet. I stoppped it.
>> Is this normal ?????
>>
>> Your help would be appreciated.
>>
>>
>
>

13 May 2005 7:06 PM

Tibor Karaszi

> I commented out the PK entry because it gives me an error that one of the fields is NOT null
> (original). Why?

Probably because the PK exists when the batch is compiled; before the first ALTER has been executed.
Try adding GO before the adding of PK.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"steve" <st***@here.com> wrote in message news:3l5he.81285$L31.712419@wagner.videotron.net...
> Hi,
> I have a table with 16 million lines. After reading about data types and importing it from Access
> i decided to change some things to save space and make the querries more efficient.
>
> I entered the following in SQL Query Analyzer:
>
> USE rafalebd
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [METEOid] [char] (8) NOT NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [date_time] [smalldatetime] NOT NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [ParamMeteo] [char] (3) NOT NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [Valide] [char] (1) NOT NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [val] [real] NOT NULL
> ALTER TABLE [tblMeasMetHor] ALTER COLUMN [f] [char] (2) NULL
> --ALTER TABLE [tblMeasMetHor] ADD CONSTRAINT pk_MeasMetHor PRIMARY KEY (METEOid, date_time,
> ParamMeteo, Valide)
> GO
>
> I commented out the PK entry because it gives me an error that one of the fields is NOT null
> (original). Why? Isn't it supposed to apply the changes line by line in order?
>
> I let it run for 34 minutes!!! and nothing yet. I stoppped it.
> Is this normal ?????
>
> Your help would be appreciated.
>