更改列:null 到 not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must be preserved.

I am looking for the specific SQL syntax to alter a column (call it ColumnA) to "not null". Assume the data has been updated to not contain nulls.

Using SQL server 2000.

解决方案

First, make all current NULL values disappear:

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL

Then, update the table definition to disallow "NULLs":

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL