3

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. :)

Kara
  • 6,115
  • 16
  • 50
  • 57
Anton Lyhin
  • 1,925
  • 2
  • 28
  • 34
  • possible duplicate of [Altering a column: null to not null](http://stackoverflow.com/questions/689746/altering-a-column-null-to-not-null) – Neil Knight Jan 30 '12 at 09:34
  • 1
    Which database are we talking about? SQL is just a query language, it doesn't specify which database you are using. Examples; MySQL, MSSQL, PostgreSQL etc. – Hubro Jan 30 '12 at 09:34
  • You shouldn't be using the visual designer - it's rather limited in many ways. Learn how to express those modification with T-SQL and execute them as statements in your Mgmt Studio - there's really no need to drop the table for this change.... – marc_s Jan 30 '12 at 10:40

3 Answers3

5

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    The last part isn't true. The `NULL_BITMAP` contains a bit for all columns regardless of nullability. [Even adding a new 9th,17th etc column doesn't require this.](http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-adding-columns-to-a-table.aspx) – Martin Smith Jan 30 '12 at 09:37
  • @MartinSmith: Thanks, misconception indeed. This links to the [full article](http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-null-bitmap-size.aspx) – Andomar Jan 30 '12 at 09:56
  • I wanna add one interesting comment. There is also an option in SQL Server menu. Just remove a tick from Options\Designer "Prevent saving changes that require table recreation". – Anton Lyhin Jan 30 '12 at 16:02
  • @Anton Will this not drop and recreate the table and loosing the data in the process? –  Sep 13 '15 at 10:58
1

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

Max
  • 804
  • 7
  • 19
1

This might help:

ALTER TABLE [Table] ALTER COLUMN [Column] [YourDataType] NULL
Arion
  • 31,011
  • 10
  • 70
  • 88