CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rafael Kayser Smiderle
Rafael Kayser Smiderle

Reputation: 690

SqlServer performance Alter column from not null to null

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?

Upvotes: 2

Views: 928

Answers (2)

NickyvV
NickyvV

Reputation: 1746

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

Upvotes: 4

Jithin Shaji
Jithin Shaji

Reputation: 6073

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

Upvotes: 4

Related Questions