Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sean_B
Posting Yak Master
111 Posts |
 Posted - 2010-01-12 : 06:37:25
|
| Hi,I've a big table with 420 million rows, I need to add a new column (bit type) with a default value of zero not null.I know there are several different approaches to this but I believe that the best way to do it is1 - have recovery in simple mode2 - add the column without any constraints3 - Update the column in stages(I'm doing 1 million rows at a time), to prevent the transaction log getting to big.4 - Add the constraintsI've done this and adding the constraint at the end still took 4 hours even though all the values where set to 0.Can anyone suggest a faster approach than this, as far as I'm aware this is the best approach.thanks SeanSean |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-12 : 07:14:29
|
1. Script out all contraints and indexes for the old table.2. Create a new table with same ddl and add the bit datatype.3. Insert all records into the new table with 0 for the bit column in the select statement4. SP_RENAME old table to oldertable5. sp_rename newtable to oldtable6. reapply contraints and indexes.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 07:18:06
|
Not tried this on a large table, so I'm speaking without experience.I think that BIT fields are aggregated into binary objects (WORDs or DWORDs I suppose, in a physical sense) and thus the database rows only need extending if you are unlucky and either a) this is the first bit field or b) you've just rolled over to needing another WORD/DWORD.The fact that you need NOT NULL is good - because otherwise SQL has to allocate more space to record the NULL'ness of the Bit.And I'd hazard a guess that default value of zero is good too, because hopefully that's the default state for unused spare bits (I'm guessing again).I'm also guessing that SQL is smart enough to know that it doesn't need to initialise a spare bit to its default value ...So on that basis I think this will work instantly - but YMMV  - possibly hugely  ALTER TABLE dbo.MyTable ADD MyNewColumn bit NOT NULL CONSTRAINT DF_MyNewColumn DEFAULT 0GO |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 07:20:12
|
| @Peso - that's a whole heap of work to add a bit field, no?SQL is happy to add a column without new table / copy data / rename steps, so I'm guessing you have some insider knowledge on the performance of your method on large tables?? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 07:31:30
|
| This refers:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108166which implies that adding a NULLABLE column is instant (I'm assuming this covers BIT datatype, that post didn't include testing for that).A DEFAULT may also be instant - won't be applied until the row is next updated (assuming I've read that right).So that just leaves you with needing to make it NOT NULL. Use Trigger / Constraint / something to process any new data, and leave it at that?I'm still guessing though ... I should shift-bum and dig out a mega-million row table. Let me check which of my client's DBs is busiest at the moment for a real world test |
|
|
|
Sean_B
Posting Yak Master
111 Posts |
Posted - 2010-01-12 : 08:26:57
|
| Thanks for the responses guys, I'm thinking of not adding the not null constraint as I'm adding a default anyway.Sean |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 08:35:38
|
| The NOT NULL saves you an entry in the bitmap table for NULLs - so best to have NOT NULL if you aren't going to store NULL values in your data (it will also help by giving a Hint to the query optimiser).However, I don't know if NOT NULL will force an immediate update of all rows ...If you set a DEFAULT then all rows have to be set - although as I read it that may be deferred until the next UPDATE of the row. |
|
|
|
|
|
|
|
|
sean_b