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

Adding a novel column to an existing tabular array amongst information is ever tricky. You require to know what information is there, how much information is there, to approximate how long your interrogation is gonna receive got to consummate inwards production. Also, yous cannot add together NOT NULL columns into an existing tabular array if they are non empty together with yous don't receive got a default value specified. If yous know SQL thus yous likely know that yous tin laissez passer on the sack add together columns to an existing tabular array inwards SQL Server using ALTER command. It non solely allows yous to add together a column but to driblet columns every bit well. You tin laissez passer on the sack likewise add together or driblet constraints using ALTER command. Btw, yous require to last careful acre doing anything amongst existing tables because of information inside, which presents some challenges acre adding novel columns or dropping existing ones.

You likewise require to last careful virtually how long your interrogation is going to receive got to consummate inwards production. For example, when yous add together novel columns to existing tabular array e.g. suppose your tabular array contains 10M rows than adding a novel column would last a lilliputian flake ho-hum because default value has to last populated on those 10M rows, it volition likewise send upon indexes, database stats etc. Anyway, In this article, yous volition larn virtually SQL query or ascendancy to add together novel columns into existing tabular array inwards SQL Server 2008, 2012 together with other versions.

If yous are learning SQL or working amongst Microsoft SQL Server every bit a developer together with learning inwards hotch potch fashion on the require to know basis, I propose yous read at to the lowest degree 1 proficient majority on SQL e.g. Head First SQL together with some other proficient 1 on SQL Server to know the fundamentals well. Expanding on that cognition volition thus brand  a lot of feel because yous already know how to solve the problem.

SQL interrogation to add together columns into existing table

As I said yous tin laissez passer on the sack role ALTER SQL clause for modifying an existing table. You tin laissez passer on the sack role next SQL interrogation to add together columns into an existing tabular array inwards SQL Server 2008:

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

When yous add together a novel column to existing tabular array amongst data, it's of import yous laissez passer on a default value or become far NULLABLE, every bit yous require some values to last used amongst existing rows.

If yous brand the hobbies column NOT NULL thus the inwards a higher house interrogation volition non move every bit shown below:

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

ALTER TABLE solely allows columns to last added that tin laissez passer on the sack 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 atmospheric condition are satisfied the tabular array must last empty to allow improver of this column. Column 'hobbies' cannot last added to non-empty tabular array 'StudentRecords' because it does non satisfy these conditions.

If yous desire to brand certain it's non naught thus yous must supply a default value every bit shown below:

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

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

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

This interrogation adds ii column, offset hobbies together with 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 ii columns customer_id together with customer_name together with 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, yous should simply advert the column id, advert together with address. Customer information is redundant because yous 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, yous 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 yous receive got to make the tabular array empty or yous require to supply  a default value e.g. empty addresses every bit shown below:

You tin laissez passer on the sack run across that this fourth dimension the ALTER disceptation is successful together with yous tin laissez passer on the sack likewise run across the customer_address column added amongst 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 push clit a fast 1 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 yous have. You likewise receive got limitation upon adding NOT NULL constraints every bit yous must supply a default value to last used for existing rows.

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

Share this post