Dinesh Asanka
database size and transaction log file after adding a column with default constraint using SQL Server ALTER TABLE Add statement.

SQL Server ALTER TABLE ADD Column overview

August 27, 2019 by

In this article, we will explore SQL Server ALTER TABLE ADD Column statements to add column(s) to an existing table. We will also understand the impact of adding a column with a default value and adding and updating the column with a value later on larger tables.

As a database developer, you need to add columns to the existing tables too offen. You would think that adding a column to the SQL Server database table would not be a major deal. Sometimes you might be adding the column from the SQL Server Management Studio itself. Well, that would be fine for a small table or a table which does not have a large number of transactions. Let’s go ahead and see how we can add columns to an existing table and also understand the reasons and the best practices to add a column to a table that is large in size.

Sample Table

Let us create a sample table with sufficient data set from the following script.

The above script will create a sample table called SampleTable. Data fields are added so that the large size table will be created. Next, a large number of records were added by executing the following query multiple times.

After the above query is executed, 500,000 records are updated to the SampleTable. After executing the above query following is the table size and other parameters for the table.

This can be retrieved by sp_spaceused ‘SampleTable’

Table size and other parameters

The following is the database size for data and the log file.

Database file sizes, transaction log file.

Let’s quickly go over the syntax of adding one column to an existing table by using ALTER TABLE ADD statement as shown below.

You can use the below statement to add column NewColumn1 to our table SampleTable.

Also, you can add multiple columns to a table using the single SQL Server ALTER TABLE statement as below.

When adding a column to a large table, typically you want to add the column and fill the added column with a default value. For example, if you want to add a status column, next is to fill the column with a default value.

Adding a Column with a Default Constraints

To achieve the above objective, the easiest way is to add a column with a default constraint. When the default column is added, the default value will be added to the table. The following script makes use of SQL Server ALTER TABLE ADD Column (Status in our case) statement to add a column named Status with default constraint.

When the column is added as above, the Status column will be added with the Value INC for all records.

From the profiler following statics are captured via SQL Profiler during the column addition with default values.

CPU

0

Reads

185

Writes

0

Duration Mille Seconds

65

You can see that the column is added to the table even less than one second and operation is very minimal cost.

The following are the locking stats during the column that are added with a constraint.

Resource Type

Resource Subtype

Request Mode

Records Count

DATABASE

S

1

DATABASE

DDL

S

1

KEY

X

11

METADATA

DATA_SPACE

Sch-S

1

OBJECT

IX

6

OBJECT

Sch-M

2

Please note that the following query should be executed in an open transaction in order to capture the above locking statistics.

This shows that Table (Object) has intended Exclusive lock which means that the table is not exclusively locked during the addition of the column. Also, adding a column with default value has not taken even a one minute though it has 500,000 records.

Let us see the table size.

Table size after adding a column with default constraint using SQL Server ALTER TABLE clause.

You will see that nothing has changed.

Let us see the file sizes of the database.

database size and transaction log file after adding a column with default constraint using SQL Server ALTER TABLE Add statement.

Nothing much has changed to the data file as well as for the log file. All of these results indicate that adding a column with a default constraint will result in only a metadata change.

Update with a Value

Let us update the same column with a different value and let us get the same stats.

Resource Type

Request Mode

Records Count

DATABASE

S

1

OBJECT

X

1

The above table shows that the table is exclusively locked which means that the table is not accessible during the update.

Let us look at the table size.

Table size after adding and updating the column,

The table has grown by some value as shown in the above figure.

The following are the details for the query expenses captured from the SQL Profiler.

CPU

29,781

Reads

9,188,986

Writes

527,436

Duration Mille Seconds

1,113,142

Evidently, when updating a column for a large table, resource consumption is high.

The following is the database file sizes when the column is added and updated the values.

database size and transaction log file after adding a column and updating the column.

These stats show that there is a remarkable difference between adding a column with a default value and adding a column and updating the column with a value later. During the column update, the transaction log will grow and exclusive locking will be placed on the table prohibiting any reads or writes to the table.

What is the difference in these scenarios? Prior to the SQL Server 2012, when adding a column with default value will cause the same behavior. In SQL Server 2012 and onwards, this is no longer the situation, the column is added online to the table and no update occurs and it is only a metadata change.

How This is Achieved

This is achieved by a somewhat very simple but novel approach. sys.system_internals_partition_columns DMV has two additional columns named has_default and default_value as shown below.

sys.system_internals_partition_columns dmv

So when the column added with a default value, it will not update the data page instead it will update this system table. When a row is updated, then the default value will be pushed to the table even if the default value column is not updated.

Now, the next question is what if the Default constraint is dropped just after it is created. In that scenario, still, the above setting will prevail hence the previously set default value will be kept.

Conclusion

We covered the basic syntax of SQL Server ALTER TABLE in this article and implemented it to add columns to an existing table.

Before SQL Server 2012, there was no difference between adding a column with the default and adding a column and updating it. Therefore, in the prior SQL Server 2012 era, rather than adding a column with a default constraint, it is better to add a column and updating with batches so that the table is not exclusively locked.

However, from SQL Server 2012 onwards, the approach has changed so that adding a column with default constraints is much better.

Dinesh Asanka

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka
Dinesh Asanka

Latest posts by Dinesh Asanka (see all)

4,095 Views