|
|
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.
[Back to original message]
|