SqlServer performance Alter column from not null to null

Question

For some reason, I need update one of my table columns from "NOT NULL" to "NULL". The command is simple:

ALTER TABLE TBLOGDOCMESSAGE ALTER COLUMN PROCESSID BIGINT NULL

The problem is that the command is taking to much time to run. My table contains about 30M registers (but my datacenter could have more than 120M registers). The column has a non clustered index on it and is not a FK.

There is a way of speeding up the command in SQLServer 2008 and up?

You could check here beyondrelational.com/modules/24/syndicated/501/posts/12769/… to see if this fits your scenario — NickyvV
– NickyvV, Commented May 28, 2014 at 12:57
No, but define "too much time". SOmething like this on a non trivial machine should take - hm - a couple of minutes maximum. — TomTom
– TomTom, Commented May 28, 2014 at 13:04
@TomTom, Intel Core i5, 8gb Ram take more than 2 hours with 30M registers. — Rafael Kayser Smiderle
– Rafael Kayser Smiderle, Commented May 28, 2014 at 13:07

NickyvV · Accepted Answer · 2014-05-28 13:12:14Z

4

An important lesson is the existence of a NULL bitmap.

A NULL bitmap will always be there in a table (heap or clustered table) irrespective of whether the table has NULLable columns or NOT. Note that we defined a UNIQUE constraint on LastName + FirstName in Demo 2 and UNIQUE constraint is enforced using a UNIQUE INDEX on those columns. NULL bitmap will NOT be present in a NON-CLUSTERED INDEX if all the columns part of the index definition are defined as NOT NULL. In our case both LastName + FirstName are defined as NOT NULL in the table and that's why NULL bitmap wasn't there initially. When we ALTERed the table definition, in this case the operation has to touch each and every row of the table and that's why it is an expensive operation. (link)

Because your column also has a nonclustered index, the NULL bitmap is not yet present.
So I think dropping the nonclustered index, then issue the ALTER TABLE statement and recreating the index is probably faster here, or at least worth trying (in a safe environment :).

edited May 28, 2014 at 13:12

answered May 28, 2014 at 13:01

NickyvV

1,7522 gold badges16 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rafael Kayser Smiderle Over a year ago

I've think of it, and I will test it now. My fear is the time to recreate the index will take as much longer than running the command without dropping it. I will let you guys know about the result.

Rafael Kayser Smiderle Over a year ago

Dropping the index first do the trick. It ran the command almost instantaneously. Thanks.

Jithin Shaji · Accepted Answer · 2014-05-28 12:57:29Z

4

I think, dropping indexes may speedup your script. Once altered, recreate the Indexes.

answered May 28, 2014 at 12:57

Jithin Shaji

6,1015 gold badges27 silver badges47 bronze badges

Collectives™ on Stack Overflow

SqlServer performance Alter column from not null to null

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related