Set column to NOT NULL on large table

Question

Post reply

Set column to NOT NULL on large table

Phil Parkin

SSC Guru

Points: 247155
More actions
February 2, 2012 at 3:21 am

#391462

We have a production database which contains a nullable column which we would like to make non-nullable.
The table has 95 million rows and occupies 30GB on disk.
All NULLs have been eradicated in the column to be changed.
I decided to test the change in QA.
Here's the way I have scripted the change (I had to drop an index which referenced the column, apply the not-nullable change, then rebuild the index):
use <DBName>
go
if exists ( select *
from sys.indexes
where object_id = object_id('dbo.<Tablename>')
and name = 'IX_<index_name>' )
drop index [IX_<index_name>] on [dbo].<Tablename> with ( ONLINE = OFF )
GO
alter table dbo.<Tablename>
alter column <ColumnName> varchar(5) not null
go
create nonclustered index [IX_<index_name>] on [dbo].[<tablename>]
(
[<ColumnName1>] asc,
[<ColumnName>] asc, --this is the column whose definition is being changed
[<ColumnName2>] asc
)
include ( [<ColumnNam3>]) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) on [PRIMARY]
GO
This test highlighted the following issues:
1) While the column's definition is being changed, most processes which access the database are locked out. That's more than 30 minutes down-time.
2) The transaction log grew by more than 50 GB.
I'd be interested to hear any ideas about how to get this change applied with less impact (less downtime & less effect on the TLog). I've started thinking about building an exact copy of the existing table definition, but with the non-nullable column and then copying the data into that.
The hard bit happens when switching around table names / FK constraints and possibly other stuff when the data has been populated. Before I go down that avenue, I thought I'd get input from people here - thanks very much for any comments.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 247155 More actions · Answer 1

Phil Parkin

SSC Guru

Points: 247155

February 2, 2012 at 10:21 am

#1441956

Silence! Any ideas anyone?

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

I'd recommend the second approach.

Use sp_rename for changing the names once done?

Dave Morrison SSCrazy Points: 2077 More actions · Answer 3

Hi Phil, if you're looking to minimise the down time I'd suggest partition switching

Phil Parkin SSC Guru Points: 247155 More actions · Answer 4

Thank you very much for the responses - I'll look into the partition switching idea.

Phil

Aditya Kota Ten Centuries Points: 1392 More actions · Answer 5

Partition Switching will fail as the Main and Switch Table should be identical(not just no. of columns but also their properties).

"sp_rename" is a safe option.

Phil Parkin SSC Guru Points: 247155 More actions · Answer 6

Thanks for the response. I got there in the end by a slightly devious method - I did an alter table with nocheck and added a Check constraint on the column (after fixing up any existing nulls).

Job done - in a flash.

Of course, any future developers who inherit this table will shrug their shoulders and wonder who the idiot was who did it that way :I)

I'll have to live with the guilt.

Set column to NOT NULL on large table

Cookies on SQLServerCentral