SSMS Method
A new column with Default Value can also be added to an existing table from SQL Server Management Studio (SSMS) using ‘New Column’ menu Option from object explorer window as below. In this example we are adding a new Text column ‘RoleAssigned’ with default value to EmployeeData table.
Steps for for Adding a New Column with default value to an existing table from SSMS as below:
1. Goto Object Explorer in SSMS
2. Select Databases and click the + On the left.
This will list all avilable databases. Here we will select the database ‘DemoDB’
3. Click the ‘+’ left of ‘DemoDB’. This will list all database objects available in the selected database.
4. Click on the ‘+’ left of Tables object.
This will list all Tables available in the database.
Here we will select the Table name ‘EmployeesData’ and click the ‘+’ on the left of this table.
This will list all the table objects like Columns, Keys, Constraints, Triggers, Indexes and Statistics.
5. Click on the + left of Columns object under the EmployeesData table.
This will display all Columns created under EmployeesData Table.
6. Right click on the ‘Columns’ object and this will display the popup menu with menu option ‘New Column’.
7. Select the menu ‘New Colum’ from the popup menu.
This will display the window with options to edit the columns in the Employeesdata table.
Input the new column name ‘RoleAssigned’ and select data type ‘varchar(50)’ as below
8. Select this new Column ‘RoleAssigned’ and go to the ‘Column Properties’ section below.
Add ‘Role Not Assigned’ aginst ‘Default Value or Binding’ under the properties section.
9. Goto ‘Save’ icon on top menu and click to save all the changes done to create the new text column ‘RoleAssigned’
10. Right click on the ‘Columns’ object under the EmployeesData table and select ‘Refresh’. You can see the new column ‘RoleAssigned’ which we have created.
How to Add a Column with a Default Value to an Existing Table in SQL Server
In SQL Server sometimes it may be required to set the default value to a Column in a table. This default value will be set as the column value when there is no value inserted or left empty for that particular column while data is inserted into the Table. Sometimes after creating a table, it may be required to add a New Column with Default Value based on new requirements or the developer missed it to add a specific Column when the database table schema was initially created.
In this article, we will discuss about adding a New Column with a Default value to an existing Table in SQL Server.