How To Add Together Columns To An Existing Tabular Array Inwards Sql Server

Adding a novel column to an existing tabular array alongside information is ever tricky. You bespeak to know what information is there, how much information is there, to justice how long your inquiry is gonna accept to consummate inwards production. Also, you lot cannot add together NOT NULL columns into an existing tabular array if they are non empty as well as you lot don't receive got a default value specified. If you lot know SQL as well as hence you lot in all probability know that you lot tin add together columns to an existing tabular array inwards SQL Server using ALTER command. It non entirely allows you lot to add together a column but to driblet columns every bit well. You tin likewise add together or driblet constraints using ALTER command. Btw, you lot bespeak to endure careful piece doing anything alongside existing tables because of information inside, which presents some challenges piece adding novel columns or dropping existing ones.

You likewise bespeak to endure careful virtually how long your inquiry is going to accept to consummate inwards production. For example, when you lot add together novel columns to existing tabular array e.g. suppose your tabular array contains 10M rows than adding a novel column would endure a trivial fighting ho-hum because default value has to endure populated on those 10M rows, it volition likewise impact indexes, database stats etc. Anyway, In this article, you lot volition larn virtually SQL query or dominance to add together novel columns into existing tabular array inwards SQL Server 2008, 2012 as well as other versions.

If you lot are learning SQL or working alongside Microsoft SQL Server every bit a developer as well as learning inwards hotch potch mode on the bespeak to know basis, I propose you lot read at to the lowest degree 1 skillful majority on SQL e.g. Head First SQL as well as some other skillful 1 on SQL Server to know the fundamentals well. Expanding on that cognition volition as well as hence brand  a lot of feel because you lot already know how to solve the problem.

SQL inquiry to add together columns into existing table

As I said you lot tin purpose ALTER SQL clause for modifying an existing table. You tin purpose next SQL inquiry to add together columns into an existing tabular array inwards SQL Server 2008:

ALTER TABLE dbo.StudentRcords ADD hobbies VARCHAR(50) NULL;
 

When you lot add together a novel column to existing tabular array alongside data, it's of import you lot plow over a default value or acquire inwards NULLABLE, every bit you lot bespeak some values to endure used alongside existing rows.

If you lot brand the hobbies column NOT NULL as well as hence the to a higher house inquiry volition non function every bit shown below:

ALTER TABLE dbo.StudentRcords ADD hobbies VARCHAR(50) NOT NULL;
 

ALTER TABLE entirely allows columns to endure added that tin incorporate nulls, or receive got a DEFAULT Definition specified, or the column beingness added is an identity or timestamp column, or alternatively if none of the previous weather condition are satisfied the tabular array must endure empty to allow add-on of this column. Column 'hobbies' cannot endure added to non-empty tabular array 'StudentRecords' because it does non satisfy these conditions.

If you lot desire to brand certain it's non naught as well as hence you lot must furnish a default value every bit shown below:

ALTER TABLE dbo.StudentRcords ADD hobbies VARCHAR(50) NOT NULL DEFAULT "Cricket";

By the way, you lot tin likewise add together to a greater extent than than 1 column into existing tabular array inwards unmarried ALTER query. You simply bespeak to split upwards them via comma every bit shown below:

ALTER TABLE dbo.StudentRcords ADD hobbies VARCHAR(50) NULL, acitivity_score INT NULL;

This inquiry adds 2 column, commencement hobbies as well as minute activity_score into StudentRecords table.

SQL Server ALTER TABLE ADD COLUMN Example

Now, let's run across a alive instance where nosotros volition endeavour to add together a column into existing tabular array using SQL Server Management Studio Query Editor:

We receive got an existing tabular array called Test.dbo.Customer, which has 2 columns customer_id as well as customer_name as well as nosotros volition add together a novel column called customer_address into the table. By the way, don't follow this naming convention, instead of customer_id, you lot should simply advert the column id, advert as well as address. Customer information is redundant because you lot know they are client information every bit they are inwards the Customer table. 

Everything went fine because nosotros chose the constraint every bit NULL, had nosotros receive got made customer_address NOT NULL, you lot would receive got got the below fault because the existing tabular array is non empty.

In gild to add together NOT NULL constraint on a novel column into an existing table, either you lot receive got to make the tabular array empty or you lot bespeak to furnish  a default value e.g. empty addresses every bit shown below:

You tin run across that this fourth dimension the ALTER disceptation is successful as well as you lot tin likewise run across the customer_address column added alongside empty values. To larn to a greater extent than about ALTER command, read SQL Server 2012 T-SQL Fundamentals.


That's all virtually how to add together a novel column into an existing tabular array inwards SQL Server. It'an ever play a joke on if your existing tabular array is non empty. In fact, something adding a novel column inwards production database takes quite a long fourth dimension depending upon how much information you lot have. You likewise receive got limitation upon adding NOT NULL constraints every bit you lot must furnish a default value to endure used for existing rows.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post