Re: An ALTER TABLE on a huge table...
From: AnthonyThomas (Anthony.Thomas_at_CommerceBank.com)
Date: 12/14/04
- Next message: AnthonyThomas: "Re: Move System BD's?"
- Previous message: AnthonyThomas: "Re: Transaction within stored procedure?"
- In reply to: Tom Moreau: "Re: An ALTER TABLE on a huge table..."
- Next in thread: Tom Moreau: "Re: An ALTER TABLE on a huge table..."
- Reply: Tom Moreau: "Re: An ALTER TABLE on a huge table..."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 13 Dec 2004 20:06:59 -0600
Do not use a cursor though, you can use a TOP clause for those remaining
with the field not equal to 0 to do your batching. You will get the same
effect but it will run more efficiently.
UPDATE tbl
SET NewFld = 0
FROM dbo.TableA AS tbl
WHERE tbl.key IN(
SELECT TOP 10000 key
FROM dbo.TableA
WHERE NewFld IS NULL
OR New FLD <> 0
)
Or some variation thereof.
Sincerely,
Anthony Thomas
-- "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:%23OaG56V4EHA.4004@tk2msftngp13.phx.gbl... You're on the right track. You can use a looping solution, whereby you are updating blocks of rows, e.g. 10,000 at a time. This can run as a background process until the entire job is done. At that point, kick out your users and alter the column from NULL to NOT NULL. -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com "James Hunter Ross" <james.ross@oneilsoft.com> wrote in message news:uGTn03V4EHA.2012@TK2MSFTNGP15.phx.gbl... Friends, we are adding a INT NOT NULL field to a table containing millions of records, and we must provide a default value, 0. This will be run as part of an upgrade procedure for customers in the field, amny of which have not DBA and no on-site SQL Server expertise.. We know (based on our own in-house experiments) for large tables the transaction log growth will be huge, and perhaps too much for some customers machines. We can consider adding the field as NULL-able, switching to "simple" recovery (or the SQL 7 equivelent, if SQL 7 is used), updating the newly added field to 0 in all rows using a cursor, then swithcing back to the original recovery model. We have consider variations on this idea, and other ideas too, but I want to be sure that nobody else out there has come up with anything better. Does anybody have some creative approach that we might consider? Perhaps BCP out, truncate table, alter table, BCP back? Or, anything else? We don't want to deviate from the NOT NULL characteristic of the new field. Any ideas will be greatly appreciated! Thanks in advance for your words, James Hunter Ross Senior Software Developer O'Neil Software, Inc. james.ross@oneilsoft.com
- Next message: AnthonyThomas: "Re: Move System BD's?"
- Previous message: AnthonyThomas: "Re: Transaction within stored procedure?"
- In reply to: Tom Moreau: "Re: An ALTER TABLE on a huge table..."
- Next in thread: Tom Moreau: "Re: An ALTER TABLE on a huge table..."
- Reply: Tom Moreau: "Re: An ALTER TABLE on a huge table..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
