alter table - change time evaluation

Hi,

I have a table with 70 million records. I want to change the datatype of one of the columns from int to decimal(18,3). What is the best way of doing it? and how can I evaluate the time change before I start and make the change?

thank you,

Tomer

..assuming you want to know how long the operation would take....this varies massively by table definition + indexes + hardware + load

you could use SELECT INTO to create a table of one million records and add indexes identical to those on the production table.

Then drop the index on the int column on that table,

execute an ALTER TABLE ALTER COLUMN to change the datatype

and rebuild the index.

Then multiply the result by 70 though that's only an estimate as the rebuild time may not be linear.

ALTER how long should it take?

The following ALTER takes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.PERSON ADD
FL_CNSL_NTFY char(1) NOT NULL CONSTRAINT DF_PERSON_FL_CNSL_NTFY
DEFAULT '',
CD_INTRP_NEED smallint NOT NULL CONSTRAINT DF_PERSON_CD_INTRP_NEED
DEFAULT 0
GO
COMMIT

Thanks for any tips on this issue...(cuneyt.barutcu@.illinois.gov) writes:

Quote:

Originally Posted by

The following ALTER takes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.

When you add non-nullable columns, SQL Server needs to rebuild the entire
table to make room for the columns, and that does take some time. But
I two hours for 2.8 million rows is more than I execpt. Then again,
it depends not only on the number of the rows, but also how wide they
are.

I don't have much experience of ALTER TABLE myself, because I almost
always take the long way in my update scripts. That is, I rename the
existing table, create the table with the new definition, copy the
data, recreate indexes, triggers, and foreign keys, move referencing
foreign keys to the new table and finally drop the old definition.
When I copy data, I have a loop, so that I copy some 50000 rows at
a time.

This way of altering a table gives more flexibility to place columns
where you want, or make changes like replacing a bit column with
a char(1) column. But it also requires more care, since there are
so many steps. I have a tool that generates this for me. If you do it
by hand, you have to be very careful.

But there is certainly one thing you should check for: blocking. Maybe
some other process is blocking ALTER TABLE from running at all. Check this
with sp_who.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jun 8, 4:17 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(cuneyt.baru...@.illinois.gov) writes:

Quote:

Originally Posted by

The followingALTERtakes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.

>
When you add non-nullable columns, SQL Server needs to rebuild the entire
table to make room for the columns, and that doestakesome time. But
I two hours for 2.8 million rows is more than I execpt. Then again,
it depends not only on the number of the rows, but also how wide they
are.
>
I don't have much experience ofALTERTABLE myself, because I almost
alwaystakethelongway in my update scripts. That is, I rename the
existing table, create the table with the new definition, copy the
data, recreate indexes, triggers, and foreign keys, move referencing
foreign keys to the new table and finally drop the old definition.
When I copy data, I have a loop, so that I copy some 50000 rows at
a time.
>
This way of altering a table gives more flexibility to place columns
where you want, or make changes like replacing a bit column with
a char(1) column. But it also requires more care, since there are
so many steps. I have a tool that generates this for me. If you do it
by hand, you have to be very careful.
>
But there is certainly one thing youshouldcheck for: blocking. Maybe
some other process is blockingALTERTABLE from running at all. Check this
with sp_who.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools. Can you also tell me how long typically takes for you
to administer this type of change.
I appreciate your help. Thanks again.|||(cuneyt.barutcu@.illinois.gov) writes:

Quote:

Originally Posted by

Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools.

It's an inhouse tool that I developed myself.

As for commercial tools on the market, I don't have a very good overview
what is available. But Microsoft offers "DataDude", that is Visual Studio
Team Suite for Database Professionals. I believe the price tag is hefty.

Many people use Red Gate's SQL Compare to generate their change scripts.

There is something called SQLFarms, which looks interesting, but I have
looked very very little on it.

Quote:

Originally Posted by

Can you also tell me how long typically takes for you
to administer this type of change.

There are two steps: 1) Implement the change script. 2) Running it.
Implementing the change script takes quite some time. But I usually
implement a whole bunch of changes at a time. Our system is a product,
which runs at some 20 customer sites, and beside the production databases
there is an unknown number of test databases. How long time it takes
running the change script depends on the size of the data base. We are
lucky in that our customers are not 24/7 shops, but if a script needs
to run for 24 hours, this is permissible. Again, keep in mind that a
script includes several table changes. Typically I would not accept two
hours to reload 2.8 million rows.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jun 11, 5:28 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(cuneyt.baru...@.illinois.gov) writes:

Quote:

Originally Posted by

Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools.

>
It's an inhouse tool that I developed myself.
>
As for commercial tools on the market, I don't have a very good overview
what is available. But Microsoft offers "DataDude", that is Visual Studio
Team Suite for Database Professionals. I believe the price tag is hefty.
>
Many people use Red Gate'sSQLCompareto generate their change scripts.
>
There is something called SQLFarms, which looks interesting, but I have
looked very very little on it.
>

Quote:

Originally Posted by

Can you also tell me how long typically takes for you
to administer this type of change.

>
There are two steps: 1) Implement the change script. 2) Running it.
Implementing the change script takes quite some time. But I usually
implement a whole bunch of changes at a time. Our system is a product,
which runs at some 20 customer sites, and beside the productiondatabases
there is an unknown number of testdatabases. How long time it takes
running the change script depends on the size of the data base. We are
lucky in that our customers are not 24/7 shops, but if a script needs
to run for 24 hours, this is permissible. Again, keep in mind that a
script includes several table changes. Typically I would not accept two
hours to reload 2.8 million rows.
>
--
Erland Sommarskog,SQLServerMVP, esq...@.sommarskog.se
>
Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi there,

you may want to check out our xSQL Object (http://
www.xsqlsoftware.com) for generating those change scripts - we have a
free lite edition available also.

Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Alter column to Varchar(max) takes to long

Hi,

I need to modify existing table in my database to varchar(max) from varchar(2000)

This table contains 30 million plus rows and has more than 70 columns.

now when i am running alter command for this it take too long(more than 9 mins) which is not acceptable. . Is their any way to reduce this execution time

Following is the query i am using for this

ALTER TABLE Receipt
ALTER COLUMN CUSTOM VARCHAR(MAX) NULL

Please let me know if you have any suggestion to improve this

TAI
Prashant

Try to add a new column with the new type and then try to do something like:

UPADTE Table
SET
NewCol = Col1,
Col1 = NULL

After that drop the old column. I don′t know if that will save you the additional space the second column will need, but it should be worth a try doing this in one step. If it does not work for you, create a column first copy the data over to the new column, then drop the old one and rename the new one. You will have to do that in a maintaince window to not procude dirty write in the new column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Alter column causing log to fill

I'm trying to simply change a column definition from Null to Not Null. It's
a multi million row table. I've already checked to make sure there are no
nulls for any rows and a default has been created for the column. My log is
set to autogrow and as the alter column colname char(6) Not Null runs the
log begins to grow. If I use no check BOL say the optimizer won't consider
the change. How can I change the nullability of a column that currently
contains no nulls without using up extreme amounts of log space?

DannyDanny (istdrs@.flash.net) writes:
> I'm trying to simply change a column definition from Null to Not Null.
> It's a multi million row table. I've already checked to make sure there
> are no nulls for any rows and a default has been created for the column.
> My log is set to autogrow and as the alter column colname char(6) Not
> Null runs the log begins to grow. If I use no check BOL say the
> optimizer won't consider the change. How can I change the nullability
> of a column that currently contains no nulls without using up extreme
> amounts of log space?

I guess the reason that the log grows, is that SQL Server needs to update
internal data structures in each page on the table. For each row there
is a bitmap that specifies which columns in the row that have the NULL
value. If you take make one column NOT NULL, then the bitmap is affected,
at least if it is not the last column in the map.

CHECK/NOCHECK has nothing to do with it, verifying the constraint does not
take any log space. (And SQL Server won't let you to say that a column is
NOT NULL without checking it, to save its own sanity.)

So the only other option to ALTER TABLE, is to take the long way: Rename
the table, create a new table, insert over, restore indexes, triggers and
constraints, move referencing foreign keys and drop the old table. When
you insert data over, you can do it in batches, and with the database
in simple recovery, the log growth will not be equally excessive. A
variation of this with even less log usage may be to bulk out the data,
and load the new table with BCP.

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

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

alter column

hi,
I have a production table with 2.6 million records, I need
to alter one column from numeric to varchar, does it will
affect the users? blocking the table? ThanksHi,
Obviously Yes....Dont do it when users are accessing the table.
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:088d01c3ced5$34b021e0$a601280a@.phx.gbl...
> hi,
> I have a production table with 2.6 million records, I need
> to alter one column from numeric to varchar, does it will
> affect the users? blocking the table? Thanks|||Changing the column data type (ALTER TABLE ... ALTER COLUMN) will acquire a
schema modification lock for the duration of the operation. In this case,
all data pages be updated so you probably want to do this during a
maintenance window.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:088d01c3ced5$34b021e0$a601280a@.phx.gbl...
> hi,
> I have a production table with 2.6 million records, I need
> to alter one column from numeric to varchar, does it will
> affect the users? blocking the table? Thanks