473,322 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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....

Jun 8 '07 #1
4 3658
(cu************@illinois.gov) writes:
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 8 '07 #2
On Jun 8, 4:17 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
(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.

Jun 11 '07 #3
(cu************@illinois.gov) writes:
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.
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 11 '07 #4
On Jun 11, 5:28 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
(cuneyt.baru...@illinois.gov) writes:
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.
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

Jun 12 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Michael Roedig | last post by:
I have a somewhat large table (abt 4 million records) and when I do an ALTER TABLE to add a column the database hangs. I have plenty of free disk space and memory. The server version is 4.0.17...
1
by: Danny | last post by:
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...
10
by: Jane | last post by:
Does any one know why this statement is failing? db2 => ALTER TABLE ELMT_T ALTER COLUMN CDTY_CD SET DATA TYPE VARCHAR(51) DB21034E The command was processed as an SQL statement because it was...
2
by: deepgun74 | last post by:
I have a blob column that is 4MB in length. I need to change it to store a 3GIG binary. 1) What is the maximum size a BLOB column can support? 2) How do i alter the size of an existing BLOB...
4
by: Jeff Kish | last post by:
Hi. I have a database I need to supply something (I'm assuming a t-sql script.. maybe something else is better) to update customer tables with. The operations include mostly changing varchar...
3
by: Jeff Kish | last post by:
Hi. I'm getting errors like this when I try to run an upgrade script I'm trying to write/test: altering labels to length 60 Server: Msg 5074, Level 16, State 4, Line 5 The object...
7
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to use ALTOBJ with this thread. I'm not going to get...
2
by: rcamarda | last post by:
Hello, I need to alter fields in all my tables of a given database, and I would to do this via a t-sql script. Example, I want to change all fields called SESSION_ID to char(6). The field is...
3
by: Rahul B | last post by:
Hi, I have a user UCLDEV1 which is a part of staff and a group(db2schemagrp1) to which i have not given any permissions. The authorizations of that user are shown as db2 =get authorizations...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.