October 25, 2005 at 2:46 pm
I have to add new column to existing table with 5 million rows. To keep all existing appl running and not to break the code I would like to assign either default value to this new field or allow NULL at min processing time (this is production server). What is faster to complete- assigning NULL or default? What sql server is doing under the hood when we assign NULL or default for column- allocate new space, check data, etc? Thanks
October 26, 2005 at 2:36 am
It depends on how the column will be used in the future.
The alter table script - adding a null column will not affect storage on disk as it added to the end of a record set. If you set the column to NOT NULL each row in the table will be modified to take this new value.. The alter table statement might take longer suing NOT NULL but don't discount it based on this as the performance and operation after the change is the most important factor.
When I have done this in the past I prefer using the NOT NULL and a default method as this makes the coding easier and removes any ambigouity about when null actually means.
Another issue could arise if you do not define a default and the app. has some lame code, whereby you have
insert into table values (val1,val2,val3)
this will break as it should be always coded..
insert into table (col1,col2,col3) values (val1,val2,val3)
So any new columns are not affected, but in some cases you can't go and change the 3rd party app.
It does depend on your scenario and how the field will be used and trying a couple of methods on a test database.
Hopefully - this answers some of the questions.
October 26, 2005 at 11:12 pm
Speaking from experience. I prefer default values in order to keep data consistent. Also for reporting purposes. I found when tracking down why certain records didn't appear in the report. Is because of the null value in the field. Changed it to a default value and it worked like a charm.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply