SQL. Changes require the table to be recreated

Question

There is a table full which contains data. Is it possible to change field from [not null] to [null] without dropping and recreating it? SQL writes that table have to be dropped and recreated. :)

Answer 1

You can try:

alter table YourTable alter column YourColumn int null

Per Martin Smiths comment, a change in nullability does not require a copy of the table. On disk, each row has a nullabiltiy bitmap at the start. I thought the bitmap had to be resized in some cases, but apparently it contains a bit for each column, nullable or not nullable.

Answer 2

Have you tried to use ALTER TABLE XXX ALTER COLUMN YYY YOURTYPE NULL command?

Answer 3

This might help:

ALTER TABLE [Table] ALTER COLUMN [Column] [YourDataType] NULL