Adding a new NOT NULL field to an existing table

Question

Adding a new NOT NULL field to an existing table

muthukrishnan.e

SSC Veteran

Points: 294
More actions
January 22, 2008 at 9:32 pm

#91639

Adding a new NOT NULL field to an existing table

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

If any data existing in the table copy the same into a temparory table. Delete the records in the table and alter the table. Insert the records into table from temporary table.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Just add the column as a nullable column. Then populate it. When you're done, alter the column to be not null.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

russell-154600 Ten Centuries Points: 1375 More actions · Answer 3

one other approach is to give it a default value, then drop the default constraint.

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 4

if you use

alter table xyz add mycol int not null default 0

keep in mind to rebuild your indexes afterward because your clusterratio may be horrible !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me