adding a new not null bit column with default 0

Question

Post reply

adding a new not null bit column with default 0

bugg

SSCertifiable

Points: 7567
More actions
March 14, 2013 at 6:32 am

#269793

I need to add a new bit column to a table with around 6 million rows.I know the table will be locked during this operation.
My question is: Is there a quick way of doing this without copying the data into a new table ,re-applying indexes extra and then doing a table name change?
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

ALTER TABLE MyTable ADD NewColumn BIT

GO

UPDATE MyTable SET NewColumn = 0

GO

ALTER TABLE MyTable ADD CONSTRAINT NewColumnDefault DEFAULT 0 FOR NewColumn

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Yes.

ALTER TABLE <table name> ADD <column name> BIT NOT NULL DEFAULT 0;

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

GilaMonster (3/14/2013)
Yes.
ALTER TABLE <table name> ADD <column name> BIT NOT NULL DEFAULT 0;

Better.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bugg SSCertifiable Points: 7567 More actions · Answer 4

GilaMonster (3/14/2013)
Yes.
ALTER TABLE <table name> ADD <column name> BIT NOT NULL DEFAULT 0;

I'm probably not being very clear, apologies, above is the way i'm currently doing it, but for a large table it is slower then expected.

That is why I was looking into creating a new table with the addtional column then moving the data etc and finally doing a table rename

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

bugg (3/14/2013)
That is why I was looking into creating a new table with the addtional column then moving the data etc and finally doing a table rename

You can do that, it'll likely be slower than the ALTER and far more work, but it'll do the job.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bugg SSCertifiable Points: 7567 More actions · Answer 6

GilaMonster (3/14/2013)
bugg (3/14/2013)
That is why I was looking into creating a new table with the addtional column then moving the data etc and finally doing a table rename
You can do that, it'll likely be slower than the ALTER and far more work, but it'll do the job.

Yes definitely slower and more work but it wont lock the table.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

bugg (3/14/2013)
GilaMonster (3/14/2013)
bugg (3/14/2013)
That is why I was looking into creating a new table with the addtional column then moving the data etc and finally doing a table rename
You can do that, it'll likely be slower than the ALTER and far more work, but it'll do the job.
Yes definitely slower and more work but it wont lock the table.

Err, I presume you;re worried about locking the table because people are using it. Is it only for reads? If you do not lock the table the "copy" and "rename" steps then if anyone modifies data in the source table during the process you could lose data when you drop the old table and rename the new one to take its place.

The method Gail showed is your best bet for performance and protect against data loss unless you are OK with losing data or you know your table is truly only ever read.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

bugg SSCertifiable Points: 7567 More actions · Answer 8

opc.three (3/14/2013)
bugg (3/14/2013)
GilaMonster (3/14/2013)
bugg (3/14/2013)
That is why I was looking into creating a new table with the addtional column then moving the data etc and finally doing a table rename
You can do that, it'll likely be slower than the ALTER and far more work, but it'll do the job.
Yes definitely slower and more work but it wont lock the table.
Err, I presume you;re worried about locking the table because people are using it. Is it only for reads? If you do not lock the table the "copy" and "rename" steps then if anyone modifies data in the source table during the process you could lose data when you drop the old table and rename the new one to take its place.
The method Gail showed is your best bet for performance and protect against data loss unless you are OK with losing data or you know your table is truly only ever read.

Good point completely overlooked the fact that the table will be written to during the read to the new table :ermm: douh!

The database is accessed 24/7. The table in question receives both a lot of reads and writes, this is my concern.

ScottPletcher SSC Guru Points: 101105 More actions · Answer 9

Can you just add the column with no default value, for now, and handle the NULLs in the code temporarily?

It's almost certainly applying the default value that is taking the time.

You can apply the default value in batches so the table is not locked for any significant period of time (assuming the table has a clustered index).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

bugg (3/14/2013)
opc.three (3/14/2013)
bugg (3/14/2013)
GilaMonster (3/14/2013)
bugg (3/14/2013)
That is why I was looking into creating a new table with the addtional column then moving the data etc and finally doing a table rename
You can do that, it'll likely be slower than the ALTER and far more work, but it'll do the job.
Yes definitely slower and more work but it wont lock the table.
Err, I presume you;re worried about locking the table because people are using it. Is it only for reads? If you do not lock the table the "copy" and "rename" steps then if anyone modifies data in the source table during the process you could lose data when you drop the old table and rename the new one to take its place.
The method Gail showed is your best bet for performance and protect against data loss unless you are OK with losing data or you know your table is truly only ever read.
The database is accessed 24/7. The table in question receives both a lot of reads and writes, this is my concern.

You should be more concerned that when you add the column that no data is lost in the process. Pick a time when you can add the column in the way Gail showed, maybe as part of a scheduled reboot or other downtime, or look at Chris' method where the column is added as NULL-able but chunk the update to fill in the column with 0's into small batches of 10K rows or so at a time so you do not block too many users while you backfill the data. In the case where you add it is NULL-able and backfill it, making the column NOT NULL will still lock the table while the engine checks every row to make sure it is NOT NULL so you'll still need some downtime. If you cannot get out of that then consider leaving it as a NULL-able column.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

bugg SSCertifiable Points: 7567 More actions · Answer 11

ScottPletcher (3/14/2013)
Can you just add the column with no default value, for now, and handle the NULLs in the code temporarily?
It's almost certainly applying the default value that is taking the time.
You can apply the default value in batches so the table is not locked for any significant period of time (assuming the table has a clustered index).

Something i've thought of, just more work then i wanted 🙂 but looks like I may have to go down this path.

bugg SSCertifiable Points: 7567 More actions · Answer 12

bugg (3/14/2013)
ScottPletcher (3/14/2013)
Can you just add the column with no default value, for now, and handle the NULLs in the code temporarily?
It's almost certainly applying the default value that is taking the time.
You can apply the default value in batches so the table is not locked for any significant period of time (assuming the table has a clustered index).
Something i've thought of, just more work then i wanted 🙂 but looks like I may have to go down this path.

Looks like it will have to be nullable, update to frontend code to handle this and apply the default in batches.

Thanks for the input guys much appreciated!

ScottPletcher SSC Guru Points: 101105 More actions · Answer 13

You could also make the "real" column name a computed column that the developers use, and based off a "dummy name" column so they don't have to deal with NULLs. You can later rename the columns once all defaults are in place so the app code doesn't have to change.

ALTER TABLE dbo.tablename

ADD real_column_surrogate bit NULL

ALTER TABLE dbo.tablename

--developers use this name in their code, as it is the "real"/permanent column name

ADD real_column AS CAST(ISNULL(real_column_surrogate, 0) AS bit)

Once the "real_column_surrogate" has all its default values in place, then you can:

Remove the computed column;

Rename the surrogate column name to the real name.

That way, developers don't have to deal with NULLs, you can still work the default values in over time, and only MOD prod code has to change later 🙂

To do INSERTs or UPDATEs, you still have to reference the real column, but all SELECTs can use the surrogate.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".