Using Query Analyser, SQL Server does not lock tables when doing a DDL statement.
Using Enterprise Manager, what happens is that EM creates a new table that resembles the one you've modified, copies the data over, removes the old table, and renames the new table to the correct table name in one transaction. As you can tell, this could take a tad bit of time and affect the usability of the database. The transaction isolation level of DTS packages can be set. ----- Original Message ----- From: Michael Kear <[EMAIL PROTECTED]> Date: Monday, March 17, 2003 3:52 pm Subject: RE: big database (need to modify columns) > I've never had anything to do with tables that large either, but if > you're worried about it exclusively locking the table, could you > DTS the > records to a new table containing the additional column, and then > afterit's successfully processed everything, drop the existing > table and > rename the new one? That would have your data locked only for a few > milliseconds, wouldn't it? Or does DTS lock the table too? > > Are you working on a live data table or a dev copy of it? > > > Cheers, > Michael Kear > Windsor, NSW, Australia > AFP Webworks. > > > > > > > > -----Original Message----- > From: Tony Weeg [mailto:[EMAIL PROTECTED] > Sent: Tuesday, 18 March 2003 2:09 AM > To: CF-Talk > Subject: RE: big database (need to modify columns) > > but from query analyzer I should be ok? > > ...tony > > Tony Weeg > Senior Web Developer > UnCertified Advanced ColdFusion Developer > Information System Design > Navtrak, Inc. > Mobile workforce monitoring, mapping & reporting > www.navtrak.net > 410.548.2337 > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Monday, March 17, 2003 9:09 AM > To: CF-Talk > Subject: Re: big database (need to modify columns) > > > Quoting Tony Weeg <[EMAIL PROTECTED]>: > > > > has anyone on here ever had to add a column or change a column's > > properties. My database already has like 15 million rows of > data in > it? > > > > if so, how the HECK did you do it? > > ALTER TABLE table ADD COLUMN column datatype; > > Never done it on something larger as 8 million rows and it takes some > time, but > it is not something that ought to break anything. Just remember that > this will > most likely exclusively lock your table, and that CF (or any other web > language) > is not the right tool for it, but you need the native DB management > tools. > > Jochem > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
