altering columns performance issues

Good Day:
When altering column width in SQL2000SP3a we find that this is taking a long
running time on tables with many rows.
We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
the data should not need to be transformed, only the column altered.
There are some 20 million rows and some 200 columns that need this
alteration.
Any thoughts on how to speed this up?
We could create new tables and copy the data, but that requires all user
activity to be stopped, and that is not palatable.
I note that Oracle can do this sort of an alter instantly, for some reason.
THANKS
Mr. Lynn Teska
Mayo ClinicHi Lynn
I just wrote an article on ALTER TABLE for SQL Server Magazine, and why some
changes take a long time and other don't. Unfortunately, it won't appear
until January, so I'll give you a sneak preview.
You are right that the data doesn't need to be transformed, but I'm not sure
what you mean by 'only the column altered'. Yes, the column needs to be
altered, and in every single row in the table. For some changes, like
changing to a smaller datatype, only the metadata needs to be changed (but
the existing data does need to be validated to make sure it 'fits' into the
smaller type.)
If you alter a column's datatype to one that needs more storage space, SQL
Server will actually make the physical change to every row to allow the
additional bytes that are needed to store numeric(8,3).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a
long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some
reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||In addition to Kalen's response:
Watch out if you use EM for this. EM generally creates a new table, copy the data etc etc instead of
executing an ALTER TABLE tblname ALTER COLUMN colname command.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lynn Teska" <lteska@.mayo.edu> wrote in message news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||Good Day:
Well here is a hypothetical situation:
We have a 24x7x365 app with a particular table that has some 200
numeric(7,2) columns.
There are a number of other columns with constraints and indexes, but none
of the numeric (7,2) columns is indexed or constrained.
The applications store new data in the table every 1 minute, or thereabouts.
There are 25 Million rows in the table.
We need to widen all the numeric (7,2) columns to numeric (8,3).
We need to minimize or eliminate application outage.
SQL server is doing a huge amount of I/O to alter the columns.
SQL server appears to only be able to alter one column at a time.
Now when I add new columns SQL server is quick as you please.
What strategy would this group think best to accomplish the task of widening
the columns?
Thanks for any insight!
Lynn Teska
Mayo Clinic
--
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyk5vqrnDHA.688@.TK2MSFTNGP10.phx.gbl...
> In addition to Kalen's response:
> Watch out if you use EM for this. EM generally creates a new table, copy
the data etc etc instead of
> executing an ALTER TABLE tblname ALTER COLUMN colname command.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > Good Day:
> >
> > When altering column width in SQL2000SP3a we find that this is taking a
long
> > running time on tables with many rows.
> > We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> > the data should not need to be transformed, only the column altered.
> > There are some 20 million rows and some 200 columns that need this
> > alteration.
> >
> > Any thoughts on how to speed this up?
> > We could create new tables and copy the data, but that requires all user
> > activity to be stopped, and that is not palatable.
> >
> > I note that Oracle can do this sort of an alter instantly, for some
reason.
> >
> > THANKS
> > Mr. Lynn Teska
> > Mayo Clinic
> >
> >
>|||Hi Lynn,
I would like to thank Kalen and Tibor for their help. As I understand, you
want to widen all the numeric (7,2) columns to numeric (8,3) in the table
on the machine. If I have misunderstood, please feel free to let me know.
To change the schema of the table, we can perform SQL statements using
Query Analyzer or Change the table schema directly in SQL Server Enterprise
Manager. Because Enterprise Manager will create a new tale and drop the
original table for changing the schema, I think it is better to use Query
Analyzer with ALTER TABLE statement.
Example:
alter table <table name> alter column <column name> numeric(8,3)
For additional information regarding ALTER TABLE, please refer to the
following article on SQL Server Books Online.
Topic: "ALTER TABLE"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I understand how to do the task with the EM or TSQL.
The problem is the time it takes with 25 million rows is not acceptable in
at 24x7x365 system.
TSQL can only alter one column at a time, and with 200 columns to do that is
not so good.
EM makes copies as you note and that is not efficient in terms of disk i/o.
I am trying to get a strategy that will minimize or eliminate down time and
blocking of the table.
Interestingly Oracle can do this sort of alteration instantly, according to
our vendor.
Thanks
Lynn Teska
Mayo Clinic
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lynn,
> I would like to thank Kalen and Tibor for their help. As I understand, you
> want to widen all the numeric (7,2) columns to numeric (8,3) in the table
> on the machine. If I have misunderstood, please feel free to let me know.
> To change the schema of the table, we can perform SQL statements using
> Query Analyzer or Change the table schema directly in SQL Server
Enterprise
> Manager. Because Enterprise Manager will create a new tale and drop the
> original table for changing the schema, I think it is better to use Query
> Analyzer with ALTER TABLE statement.
> Example:
> alter table <table name> alter column <column name> numeric(8,3)
> For additional information regarding ALTER TABLE, please refer to the
> following article on SQL Server Books Online.
> Topic: "ALTER TABLE"
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Lynn
The vendors says Oracle can do this instantly, but you've never tried it
yourself?
I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
instantly too, that's why you come to a Technical forum to find out. Have
you asked this on an Oracle Technical forum?
As I mentioned, there are some kinds of ALTER TABLEs that can be done
instantly, and there is a lot of confusion, so someone who had not
researched this issue in technical detail could easily tell you that this
was an instantaneous operation, even on SQL Server.
You are wanting to physically change 25 million rows? How could this
possible be an instantaneous operation?
One faster way you might consider is to create a new table using select
into, which is a very fast command, and then dropping the old table, and
renaming the new one to the old name.
SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
col2 ...
INTO newtable
FROM original_table
GO
--recreate constraints, indexes etc on newtable
DROP original_table
EXEC sp_rename newtable, original_table
Good Luck!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I understand how to do the task with the EM or TSQL.
> The problem is the time it takes with 25 million rows is not acceptable in
> at 24x7x365 system.
> TSQL can only alter one column at a time, and with 200 columns to do that
is
> not so good.
> EM makes copies as you note and that is not efficient in terms of disk
i/o.
> I am trying to get a strategy that will minimize or eliminate down time
and
> blocking of the table.
> Interestingly Oracle can do this sort of alteration instantly, according
to
> our vendor.
> Thanks
> Lynn Teska
> Mayo Clinic
>
> "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > Hi Lynn,
> >
> > I would like to thank Kalen and Tibor for their help. As I understand,
you
> > want to widen all the numeric (7,2) columns to numeric (8,3) in the
table
> > on the machine. If I have misunderstood, please feel free to let me
know.
> >
> > To change the schema of the table, we can perform SQL statements using
> > Query Analyzer or Change the table schema directly in SQL Server
> Enterprise
> > Manager. Because Enterprise Manager will create a new tale and drop the
> > original table for changing the schema, I think it is better to use
Query
> > Analyzer with ALTER TABLE statement.
> >
> > Example:
> >
> > alter table <table name> alter column <column name> numeric(8,3)
> >
> > For additional information regarding ALTER TABLE, please refer to the
> > following article on SQL Server Books Online.
> > Topic: "ALTER TABLE"
> >
> > Please feel free to post in the group if this solves your problem or if
> you
> > would like further assistance.
> >
> > Regards,
> >
> > Michael Shao
> > Microsoft Online Partner Support
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> >
>|||Thanks for the info.
The vended product runs on both SQL server and Oracle, I have no reason to
doubt that the change is fast on Oracle.
They seem to be experienced in both platforms.
Thanks for your suggestion I will have a look at it.
Lynn Teska
PS:
This is an expert of a note I received from an Oracle expert:
Oracle always reserves a minimum of space in every database block for
updates, determined by the PCTFREE parameter (10% of the block size by
default, which we don't modify). Hence, Oracle does not have to reserve any
extra space, it's already there. Initially the old rows will surely fit
inside the new definition, as we have just widened the columns. If some
value needs some extra space, it will use the PCTFREE space. If this is not
enough, a phenomenon called row chaining will take place, as part of the row
will have to be stored in another block, and this is not so good for
performance, but it can be attacked later on. In our case this would never
happen as we won't go updating old values.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||All the more interesting since numeric (7,2) and numeric (8,3) both appear
to be stored in 5 bytes.
Lynn
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||Yes, there does appear to be some unexpected behavior going on. I am still
researching this issue.
OTOH, I have just learned that although you are correct, that Oracle does
make this change and all ALTER TABLE changes instantly, as only a metadata
change, the tradeoff is that actual row size adjustment must be made during
actual data use. In fact, if you change the datatype size to something very
different, or smaller, like char(5) to char(3) or char to int, Oracle will
not do any validation at the time of the alter table, but will wait until
your read the row. So you can end up getting conversion errors just by
reading data, which can be very problematic.
So, like many choices, there is a tradeoff. Normally, since altering tables
is a rare occurance, but updating and modifying data is done during
production operations, I'd rather the overhead was accrued during the alter,
when I can plan for it.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> All the more interesting since numeric (7,2) and numeric (8,3) both appear
> to be stored in 5 bytes.
> Lynn
> --
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > Lynn
> >
> > The vendors says Oracle can do this instantly, but you've never tried it
> > yourself?
> >
> > I wouldn't be surprised if a SQL Server vendor said SQL Server could do
it
> > instantly too, that's why you come to a Technical forum to find out.
Have
> > you asked this on an Oracle Technical forum?
> >
> > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > instantly, and there is a lot of confusion, so someone who had not
> > researched this issue in technical detail could easily tell you that
this
> > was an instantaneous operation, even on SQL Server.
> >
> > You are wanting to physically change 25 million rows? How could this
> > possible be an instantaneous operation?
> >
> > One faster way you might consider is to create a new table using select
> > into, which is a very fast command, and then dropping the old table, and
> > renaming the new one to the old name.
> >
> > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
as
> > col2 ...
> > INTO newtable
> > FROM original_table
> > GO
> >
> > --recreate constraints, indexes etc on newtable
> >
> > DROP original_table
> >
> > EXEC sp_rename newtable, original_table
> >
> > Good Luck!
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I understand how to do the task with the EM or TSQL.
> > > The problem is the time it takes with 25 million rows is not
acceptable
> in
> > > at 24x7x365 system.
> > >
> > > TSQL can only alter one column at a time, and with 200 columns to do
> that
> > is
> > > not so good.
> > > EM makes copies as you note and that is not efficient in terms of disk
> > i/o.
> > > I am trying to get a strategy that will minimize or eliminate down
time
> > and
> > > blocking of the table.
> > >
> > > Interestingly Oracle can do this sort of alteration instantly,
according
> > to
> > > our vendor.
> > >
> > > Thanks
> > >
> > > Lynn Teska
> > > Mayo Clinic
> > >
> > >
> > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > Hi Lynn,
> > > >
> > > > I would like to thank Kalen and Tibor for their help. As I
understand,
> > you
> > > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> > table
> > > > on the machine. If I have misunderstood, please feel free to let me
> > know.
> > > >
> > > > To change the schema of the table, we can perform SQL statements
using
> > > > Query Analyzer or Change the table schema directly in SQL Server
> > > Enterprise
> > > > Manager. Because Enterprise Manager will create a new tale and drop
> the
> > > > original table for changing the schema, I think it is better to use
> > Query
> > > > Analyzer with ALTER TABLE statement.
> > > >
> > > > Example:
> > > >
> > > > alter table <table name> alter column <column name> numeric(8,3)
> > > >
> > > > For additional information regarding ALTER TABLE, please refer to
the
> > > > following article on SQL Server Books Online.
> > > > Topic: "ALTER TABLE"
> > > >
> > > > Please feel free to post in the group if this solves your problem or
> if
> > > you
> > > > would like further assistance.
> > > >
> > > > Regards,
> > > >
> > > > Michael Shao
> > > > Microsoft Online Partner Support
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > >
> > >
> > >
> >
> >
>|||OK Thanks.
Let me know what you find.
A 4 hour outage is not a plus for us.
Thanks
Lynn Teska
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Jo3UejoDHA.3040@.TK2MSFTNGP11.phx.gbl...
> Yes, there does appear to be some unexpected behavior going on. I am still
> researching this issue.
> OTOH, I have just learned that although you are correct, that Oracle does
> make this change and all ALTER TABLE changes instantly, as only a metadata
> change, the tradeoff is that actual row size adjustment must be made
during
> actual data use. In fact, if you change the datatype size to something
very
> different, or smaller, like char(5) to char(3) or char to int, Oracle will
> not do any validation at the time of the alter table, but will wait until
> your read the row. So you can end up getting conversion errors just by
> reading data, which can be very problematic.
> So, like many choices, there is a tradeoff. Normally, since altering
tables
> is a rare occurance, but updating and modifying data is done during
> production operations, I'd rather the overhead was accrued during the
alter,
> when I can plan for it.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > All the more interesting since numeric (7,2) and numeric (8,3) both
appear
> > to be stored in 5 bytes.
> >
> > Lynn
> >
> > --
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > > Lynn
> > >
> > > The vendors says Oracle can do this instantly, but you've never tried
it
> > > yourself?
> > >
> > > I wouldn't be surprised if a SQL Server vendor said SQL Server could
do
> it
> > > instantly too, that's why you come to a Technical forum to find out.
> Have
> > > you asked this on an Oracle Technical forum?
> > >
> > > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > > instantly, and there is a lot of confusion, so someone who had not
> > > researched this issue in technical detail could easily tell you that
> this
> > > was an instantaneous operation, even on SQL Server.
> > >
> > > You are wanting to physically change 25 million rows? How could this
> > > possible be an instantaneous operation?
> > >
> > > One faster way you might consider is to create a new table using
select
> > > into, which is a very fast command, and then dropping the old table,
and
> > > renaming the new one to the old name.
> > >
> > > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
> as
> > > col2 ...
> > > INTO newtable
> > > FROM original_table
> > > GO
> > >
> > > --recreate constraints, indexes etc on newtable
> > >
> > > DROP original_table
> > >
> > > EXEC sp_rename newtable, original_table
> > >
> > > Good Luck!
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I understand how to do the task with the EM or TSQL.
> > > > The problem is the time it takes with 25 million rows is not
> acceptable
> > in
> > > > at 24x7x365 system.
> > > >
> > > > TSQL can only alter one column at a time, and with 200 columns to do
> > that
> > > is
> > > > not so good.
> > > > EM makes copies as you note and that is not efficient in terms of
disk
> > > i/o.
> > > > I am trying to get a strategy that will minimize or eliminate down
> time
> > > and
> > > > blocking of the table.
> > > >
> > > > Interestingly Oracle can do this sort of alteration instantly,
> according
> > > to
> > > > our vendor.
> > > >
> > > > Thanks
> > > >
> > > > Lynn Teska
> > > > Mayo Clinic
> > > >
> > > >
> > > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in
message
> > > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > > Hi Lynn,
> > > > >
> > > > > I would like to thank Kalen and Tibor for their help. As I
> understand,
> > > you
> > > > > want to widen all the numeric (7,2) columns to numeric (8,3) in
the
> > > table
> > > > > on the machine. If I have misunderstood, please feel free to let
me
> > > know.
> > > > >
> > > > > To change the schema of the table, we can perform SQL statements
> using
> > > > > Query Analyzer or Change the table schema directly in SQL Server
> > > > Enterprise
> > > > > Manager. Because Enterprise Manager will create a new tale and
drop
> > the
> > > > > original table for changing the schema, I think it is better to
use
> > > Query
> > > > > Analyzer with ALTER TABLE statement.
> > > > >
> > > > > Example:
> > > > >
> > > > > alter table <table name> alter column <column name> numeric(8,3)
> > > > >
> > > > > For additional information regarding ALTER TABLE, please refer to
> the
> > > > > following article on SQL Server Books Online.
> > > > > Topic: "ALTER TABLE"
> > > > >
> > > > > Please feel free to post in the group if this solves your problem
or
> > if
> > > > you
> > > > > would like further assistance.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Michael Shao
> > > > > Microsoft Online Partner Support
> > > > > Get Secure! - www.microsoft.com/security
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>