Sql-server – In SQL Server what is the database cost for setting a default value on table that already has over 1mil records

default valuesql serversql-server-2012

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?

Best Answer

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.

Related Solutions

SQL Server – How to Set Default Value for All Columns with Specific Name

Let's say the new default you want is 5:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER TABLE ' + t + ' DROP CONSTRAINT ' + c + ';
  ALTER TABLE ' + t + ' ADD CONSTRAINT ' + c 
  + ' DEFAULT (5) FOR [project_id];'
FROM
(
  SELECT t = QUOTENAME(s.name) + '.' + QUOTENAME(t.name), 
    c = QUOTENAME(d.name)
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  INNER JOIN sys.default_constraints AS d
  ON d.parent_object_id = t.[object_id]
  AND d.parent_column_id = c.column_id
  WHERE c.name = N'project_id'
) AS x;

PRINT @sql;
--EXEC sp_executesql @sql;

Notes:

print is limited to 8K, so if you have a lot of tables with this column, you might not actually see the entire command output in SSMS, and it may look truncated.
This will only add the constraint to project_id columns that already have a default constraint. If you have other tables that have this column but don't currently have a constraint, you'll need a separate query.
This will not change the values already generated based on the old defaults.

Here is why I recommend against INFORMATION_SCHEMA for this type of problem:

https://sqlblog.org/2011/11/03/the-case-against-information_schema-views

SQL Server 2014 – Will Dropping Text Column Lock the Table?

I performed the DROP command and it was instantaneous.

I did not attempt to wrap the DROP and ALTER PROC in a transaction however.

Best Answer

Related Solutions

SQL Server – How to Set Default Value for All Columns with Specific Name

SQL Server 2014 – Will Dropping Text Column Lock the Table?

Related Question