change to not allow nulls

[Trevor Best]

Is there a *simple* way to change a collumn from allowing null to not
null?

I just unchecked "allow nulls" in EM and the SQL it generates to do
this one thing is astonishing, create table, drop FKs, copy data, drop
table, rename new table, rebuild FKs...

I'm saving a lot of these changes to run on another database at a later
date but would rather not require a terrabyte device to store the
script :-)

[David Portas]

Re: change to not allow nulls

I recommend you use TSQL scripts to make structure changes. You'll have
much more control and visibility over what happens and you'll be able
to test your scripts out before you go live with the change.

You can change nullability with an ALTER TABLE... ALTER COLUMN
statement but if the column is part of an index or constraint then
you'll have to drop that before you can make the change. That means
you'll also have to drop foreign keys that reference the column. EM
tries to make this easier by generating the script for you, so you
could save that script and take it as a starting point.

Another option that may be worth trying:
1. create a new column, populate it from the previous nullable one and
make it non-nullable
2. add constraints and indexes
3. drop the old column
4. rename the column you added

Possibly this method may incur less impact and downtime but that would
depend quite a lot on how the column is used and on other factors too
such as the size of the data and whether an existing index on the
column is clustered. Test it out and see.

--
David Portas
SQL Server MVP
--

[Trevor Best]

Re: change to not allow nulls


David Portas wrote:[color=blue]
> I recommend you use TSQL scripts to make structure changes. You'll[/color]
have[color=blue]
> much more control and visibility over what happens and you'll be able
> to test your scripts out before you go live with the change.[/color]

I am doing, I use EM to generate the scripts for some things if there's
a shed load to do in a table or if I don't know how to do something
(such as change a null column to a not null :-)
[color=blue]
> You can change nullability with an ALTER TABLE... ALTER COLUMN
> statement but if the column is part of an index or constraint then
> you'll have to drop that before you can make the change. That means
> you'll also have to drop foreign keys that reference the column. EM
> tries to make this easier by generating the script for you, so you
> could save that script and take it as a starting point.[/color]

Thanks, it's just a description column so alter table should do it.

[Erland Sommarskog]

Re: change to not allow nulls

Trevor Best (googlegroups@b esty.org.uk) writes:[color=blue]
> Is there a *simple* way to change a collumn from allowing null to not
> null?
>
> I just unchecked "allow nulls" in EM and the SQL it generates to do
> this one thing is astonishing, create table, drop FKs, copy data, drop
> table, rename new table, rebuild FKs...
>
> I'm saving a lot of these changes to run on another database at a later
> date but would rather not require a terrabyte device to store the
> script :-)[/color]

That is not the main problem with the scripts generated by Enterprise
Manager. The main problem is that if something goes wrong in the
middle of those scripts, you may end of with halfly-modified database,
and you may lose foreign keys forever. This is because the scripts
has a poor transaction scope.

Overall, I strongly discourage using the table designer in Enterprise
Manager to change tables. You can take the script as a starting point,
but there is a lot of problems to sort out. Some standard remedies
to apply:

o Remove all BEGIN and COMMIT TRANSACTION but the first BEGIN and last
COMMIT.
o Put all statements in EXEC('...'). (Except for calls to stored
procedures.
o Remove all GO.
o On all re-addition of foreign keys, insert WITH CHECK before
CHECK. (Yes, WITH CHECK CHCEK.)

For this particular case, adding a new column and then dropping the
old one may be a good idea, as David suggested.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp