0

NOTE: I am not a dba. :)

I have a database table that a client needs to set a column's default value. This table has 1.5mil+ records in it and this column is already populated on all existing records (they do this manually right now). They would like this column to be set automatically on all new records that their users create.

Would it cause down time or any major latency issues to set this column's default value during business hours?

5

In order to apply this change the process will need to acquire a SCH-M lock and once it's got that it should be pretty instant (a metadata only change.)

The only risk is that if you have a long running transaction that is using the table whilst the ALTER TABLE statement waits to acquire the lock any other subsequent requests coming in that require a lock on the table will be queued up behind it to prevent your alter request from being starved.

So you could end up blocking production queries for a while until the lock is granted and the change made.

|improve this answer|||||
0

If you add the new default constraint WITH NOCHECK, it will not try to apply to existing rows and will only affect new incoming rows in the future.

Actually, according to this, you don't even need WITH NOCHECK. Default constraint never apply to existing records, so you should be fine to add the constraint without creating any heavy load on your database.

|improve this answer|||||
  • 1
    This doesn't apply to defaults. They aren't checking anything. – Martin Smith Oct 3 '14 at 19:51
  • Confirmed, and edited my answer. – Tab Alleman Oct 3 '14 at 19:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy