DBHawk Tutorials

SQL Server alter table add column

When there is a need to add another column to an existing table in your SQL Server database you could write an SQL statement and then execute it. For example, adding a ‘Salary’ column to an ‘Employee’ table could be accomplished with the T-SQL command below.

ALTER TABLE [dbo].[Employee] ADD “Salary” money NOT NULL;

DBHawk can be used to visually add additional columns to existing tables. The following set of steps will have you creating new columns with ease.

*As with all methods of adding a column, locks will be created on the table preventing access from other processes. There is also a potential for significant transaction log activity when tables are modified. It is good practice to create a rollback strategy prior to making any database schema changes and to be aware of current database activity and needs.

1) Connect to the database through DBHawk using an account with the necessary permissions to modify the table.

2) Once connected to the database, go to DB Objects and expand the ‘Tables’ node. You should see your table listed underneath. Double-click the name of the table to add the column to. In the example below, the table name is ‘Employee’.

3) Click ‘Options’ then from the drop-down menu select ‘Columns’

4) Click ‘Add Column’ then you will see an empty ‘New Column’ form.

5) Fill the ‘New Column’ form to define the column’s properties, then when complete, click the ‘Add Column’ button.

• Name – Name of the column to add to the table.
• Data Type – The type of data to be stored in this column.
• Length – Used with data types that have a modifiable length. For example, the length of varchar data types. For numeric and decimal data types this field will contain the precision (maximum total number of decimal digits stored to the left and right of the decimal point). If the length is not modifiable, this field will be disabled.
• Scale – Used with numeric and decimal data types. Enter the number of decimal digits to be stored to the right of the decimal point.
• Not Null – Check this box to add the NOT NULL constraint. If checked, the column will be forced to have a value.
• Default Value (Optional) – This field can be used to specify a default value for the column. It is not necessary to use a default value. In many cases, this field will be left blank.
• Comment (Optional) – Definition or any additional comments about the column to be stored in the table’s metadata.

*DBHawk will also add the metadata data with the behind the scenes with a call to the sp_addextendedproperty stored procedure shown below.

EXEC sys.sp_addextendedproperty
@name=N’MS_Description’,
@value=N’Employee annual salary.’ ,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’TABLE’,
@level1name=N’Employee’,
@level2type=N’COLUMN’,
@level2name=N’Salary’

6) Once the form is completed, click the ‘Add Column’ in the lower right of the ‘New Column’ form. The column will now be visible in the Table Columns tab.