You are here: Re: ALTER how long should it take? « MsSQL Server « IT news, forums, messages
Re: ALTER how long should it take?

Posted by cuneyt.barutcu on 06/11/07 20:56

On Jun 8, 4:17 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (cuneyt.baru...@illinois.gov) writes:
> > 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.

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация