How To Create New Column In Sql

We often have to add a new column to existing Table/s. Let's consider we have dbo.Customer table and we are US based company and we never did business outside USA. so we never need Country Column to our existing Table for Address as we only operator in USA. Now company has decided to expand the business and might have the customers from other countries. What we want to do , we want to add column to existing table with default value. All the existing values should be also updated to default value when we add this new column. Moving forward if no Country Name provided, It should take USA otherwise value provided.

          --Create Sample Table without CountryName Column          Create          table          dbo.Customer ( CustomerId          Int          Identity(1,1), CustomerName          VARCHAR(100), StreetAddress          VARCHAR(100), City          VARCHAR(100),          State          CHAR(2))          --Insert couple of Records in Sample Table          Insert          into          dbo.Customer          Select          'Aamir Shahzad','Test Street Address','Charlotte','NC'          Union          Select          'M Raza','Test Street Address','Charlotte','NC'

Add the new column Without Providing NOT NULL Constraint

          Alter          Table          dbo.Customer          ADD          CountryName          VARCHAR(50)          Default          'USA'          Select          *          From          dbo.Customer

Fig 1: Add New Column to Existing SQL Server Table without Providing Not Null Constraint

In above query we did not mention the Not Null constraint for our CountryName column. SQL Server took that by default as Null. Also it did not update the newly added column with default values.

Add New Column With NOT NULL AND Default Constraint

If we will provide Not Null constraint for the CountryColumn and also default value. The newly added column will be populated with default values as shown below.

          Alter          Table          dbo.Customer          ADD          CountryName          VARCHAR(50)          NOT          NULL          Default          'USA'          Select          *          from          dbo.Customer

Fig 2: Add Not Null Constraint with Default Value To populate existing Column in Table

Now consider the last scenario, In which we want to keep our Column to accept Null values but populate existing records with default and add default constraint.

Add Column To Existing Table With Default Values and Column should accept Null as well. Also populate the Newly Added Column with Default Values at time of Adding Column

          Alter          Table          dbo.Customer          ADD          CountryName          VARCHAR(50)          Default          'USA'          WITH          Values          Select          *          from          dbo.Customer

Fig 3: Add New column with Null constraint and default value and populate with Default values

As you can see in last sql script we used "With values" clause. If you use that, the existing records will be populated with default values. Also as we did not provide NOT Null Constraint, our table will accept Null values in case we want to save Null value in column. If no value will be provided, default value will be inserted to the table.

          --Check if Default is working fine          Insert          into          dbo.Customer(CustomerName,StreetAddress,City,State)          Select          'John Smith','Test Street Address','Charlotte','NC'          --Insert Null CountryName          Insert          into          dbo.Customer(CustomerName,StreetAddress,City,State,CountryName)          Select          'July River','Test Street Address','Jersey City','NJ',Null