Alter Hbase Table to Add Column Family Example

In this article, nosotros volition explore SQL Server ALTER TABLE Add Cavalcade statements to add together cavalcade(s) to an existing tabular array. Nosotros volition too empathize the impact of adding a column with a default value and calculation and updating the column with a value later on larger tables.

As a database programmer, you demand to add columns to the existing tables too offen. You would retrieve that calculation a cavalcade to the SQL Server database table would not be a major deal. Sometimes y'all might be adding the column from the SQL Server Management Studio itself. Well, that would be fine for a pocket-sized tabular array or a table which does non have a big number of transactions. Let's go ahead and run into how we tin 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 usa create a sample table with sufficient data ready from the following script.

IF EXISTS ( SELECT 1 FROM Sys . tables where Name = 'SampleTable' ) DROP Tabular array SampleTable CREATE Tabular array dbo . SampleTable ( ID BIGINT IDENTITY ( ane , 1 ) NOT Nada PRIMARY Primal Clustered , DateTime4 DATETIME DEFAULT GETDATE ( ) , Column1 CHAR ( 1000 ) DEFAULT 'MMMMMMMMMMMMMMMMM' , Column2 CHAR ( 2000 ) DEFAULT 'YYYYYYYYYYYYYYYYY' )

The in a higher place script will create a sample tabular array called SampleTable. Data fields are added so that the large size table volition be created. Next, a large number of records were added by executing the post-obit query multiple times.

Set up NOCOUNT ON INSERT INTO SampleTable ( DateTime4 , Column1 , Column2 ) VALUES ( GETDATE ( ) , 'XXXX' , 'YYYY' ) Get 500000

After the higher up 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'

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

Let's speedily go over the syntax of adding one column to an existing table past using ALTER TABLE ADD argument as shown below.

Change TABLE tbl_name ADD Col_name data_type col_constraint ;

You tin use the below argument to add cavalcade NewColumn1 to our table SampleTable.

Alter Tabular array SampleTable ADD NewColumn1 varchar ( 200 )

As well, you can add multiple columns to a table using the single SQL Server Alter TABLE statement every bit below.

Modify TABLE SampleTable Add NewColumn2 varchar ( 20 ) , NewColumn3 varchar ( fifty )

When adding a column to a large table, typically you want to add the column and make full the added column with a default value. For example, if you want to add together 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 cavalcade is added, the default value will be added to the tabular array. The following script makes utilise of SQL Server ALTER TABLE Add together Column (Condition in our example) statement to add a column named Status with default constraint.

Modify Tabular array SampleTable ADD Status CHAR ( 5000 ) DEFAULT 'INC' WITH VALUES

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 cavalcade addition with default values.

CPU	0
Reads	185
Writes	0
Elapsing Mille Seconds	65

You lot tin meet that the column is added to the table even less than i second and operation is very minimal cost.

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

Resource Blazon	Resource Subtype	Request Mode	Records Count
DATABASE		S	1
DATABASE	DDL	Southward	i
Central		X	11
METADATA	DATA_SPACE	Sch-S	one
OBJECT		IX	6
OBJECT		Sch-M	ii

Please note that the post-obit query should be executed in an open up transaction in guild to capture the above locking statistics.

SELECT resource_type , resource_subtype , request_mode , COUNT ( * ) FROM sys . dm_tran_locks WHERE request_session_id = @ @ SPID Grouping Past resource_type , resource_subtype , request_mode

This shows that Table (Object) has intended Exclusive lock which ways that the tabular array is not exclusively locked during the addition of the column. Also, calculation a cavalcade with default value has not taken even a i minute though it has 500,000 records.

Permit us see the table size.

You volition encounter that nothing has changed.

Permit us run across the file sizes of the database.

Null much has changed to the information file besides equally for the log file. All of these results signal that adding a column with a default constraint will consequence in only a metadata alter.

Update with a Value

Allow us update the same column with a dissimilar value and let us get the aforementioned stats.

Resource Blazon	Request Style	Records Count
DATABASE	S	one
OBJECT	X	one

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

Let us await at the table size.

The tabular array 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
Elapsing Mille Seconds	1,113,142

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

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

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

What is the deviation in these scenarios? Prior to the SQL Server 2012, when calculation a cavalcade with default value will crusade the same beliefs. In SQL Server 2012 and onwards, this is no longer the situation, the column is added online to the tabular array and no update occurs and information technology is only a metadata change.

How This is Achieved

This is achieved past a somewhat very unproblematic but novel approach. sys.system_internals_partition_columns DMV has two additional columns named has_default and default_value as shown beneath.

So when the column added with a default value, it will not update the information page instead information technology volition update this arrangement tabular array. When a row is updated, then the default value will be pushed to the table fifty-fifty 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, all the same, the above setting will prevail hence the previously set default value volition exist kept.

Decision

We covered the basic syntax of SQL Server Change Table in this commodity and implemented it to add columns to an existing table.

Before SQL Server 2012, there was no difference between adding a cavalcade with the default and calculation a column and updating information technology. Therefore, in the prior SQL Server 2012 era, rather than adding a cavalcade with a default constraint, it is amend 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.

• Writer
• Recent Posts

Dinesh Asanka is MVP for SQL Server Category for last viii years. He has been working with SQL Server for more than fifteen 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

palmersawassin.blogspot.com

Source: https://www.sqlshack.com/sql-server-alter-table-add-column-overview/

Share this post