how expensive is a default value

Question

Post reply

how expensive is a default value

ksexton

SSCrazy

Points: 2790
More actions
November 5, 2002 at 9:11 am

#63215

I have a fat 40 million row table. I looked at the explain plan for this sql (add new column with a default value). It doesn't look like it would be expensive but when I look at the sql I see "an update" which should be very expensive.
ALTER TABLE dbo.table1 ADD CONSTRAINT
DF_t1_colz DEFAULT 'mmm' FOR colz

Viewing 7 posts - 1 through 6 (of 6 total)

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

dj_meier SSCrazy Points: 2732 More actions · Answer 1

The only thing I could find on this says that: constraints require less overhead than triggers, rules, and defaults.

-Dan

Mkumari Hall of Fame Points: 3498 More actions · Answer 2

I think both adding the constraint and the update does the same.What I suggest is do the updates in chuncks i.e. say 1000 - 5000 at one go and a gap of 15 Sec. and than update another block of records.This way the users who are using the table will not get blockedout.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

Seems to me that adding the column, is one function and adding the constraint is another. If you add the column, then chunk up the updates to set the default value to all the existing records as already suggested, then add the constraint last, then adding the constraint should not cause a performance hit, since all records already have a value for the new column. Also by specifying your new column to accept null values, then no update will occur on all existing rows, unless you specify the WITH VALUE clause (I think, from reading the documentation).

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

ksexton SSCrazy Points: 2790 More actions · Answer 4

I am the original poster:

This is really what I need to run but I have no idea what it is doing?

ALTER TABLE dbo.central_fact_Table ADD

MED_COM char(10)DEFAULT 'Commercial'

Mkumari Hall of Fame Points: 3498 More actions · Answer 5

The code which you gave is actually trying to create a new column with a default value of Commercial on the central_fact_table table.

Code:

ALTER TABLE dbo.central_fact_Table ADD

MED_COM char(10)DEFAULT 'Commercial'

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 6

YOu would be better off renaming the old table, dropping the constraints, then recreate the table with the new column, populate it with an insert into and then add the indexes and constraints.

Updates are more expensive than inserts when doing it on all the table.

Consider that adding a char(10) results in each row being extended by 10 bytes, this will cause lots of page splits and thus index remapping. Thus the use of a straight insert into statement is better.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons