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. 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. 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. 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. The following is the database file sizes when the column is added and updated the values. The following are the details for the query expenses captured from the SQL Profiler.Įvidently, when updating a column for a large table, resource consumption is high. The table has grown by some value as shown in the above figure. The above table shows that the table is exclusively locked which means that the table is not accessible during the update. Let us update the same column with a different value and let us get the same stats. All of these results indicate that adding a column with a default constraint will result in only a metadata change. Nothing much has changed to the data file as well as for the log file. Let us see the file sizes of the database. Also, adding a column with default value has not taken even a one minute though it has 500,000 records. This shows that Table (Object) has intended Exclusive lock which means that the table is not exclusively locked during the addition of the column.
0 kommentar(er)
