SQL Server Alter Table with Add Column

There are many scenarios where we need to do structural changes to a table, i.e. adding a column, modifying data type, dropping a column and so on. This change to an existing table is possible by issuing a SQL Server Data Definition Language (DDL) T-SQL command known as Alter Table.

In this article we are specifically focusing on adding a new column in a table using SQL Server Alter Table.

Scenario 1: Adding columns to empty table

When the table is created and if there are no records in that table, then there is no problem in adding a column or multiple columns to that table.

Let us see the following example:

create table customers1
(
  custid varchar(5),
 custname varchar(50)
)
go

Scenario 1.a: Adding single column:

After creating the table, we realize that date of birth (DOB) column needs to be added. The following command will help us in achieving this.

Alter Table customers1
Add DOB Date
go

Scenario 1.b: Adding multiple columns:

Adding two more columns is also very easy. Let us add nationality and gender as two more columns by just having a comma separator in each definition.

Alter Table customers1
Add Nationality Varchar(40), Gender Char(1)
go

Scenario 1.c: Adding columns with constraints:

Adding a column with constraint such as primary key, unique key, foreign key, not null, check or default is again not challenging in an empty table.

Note – A primary key constraint cannot be added on the new column if there is already a primary key on any other existing column.

Let us add SSN and deposit amount as two columns, SSN having primary key and deposit amount having check constraint.

Alter Table customers1
Add SSN Varchar(10) Primary Key, 
Deposit_Amount Integer Check(Deposit_Amount >= 200)
go

Scenario 2: Adding columns to table having records

When a column is added to a table with existing records, then for those records, by default a null value is assigned by SQL Server.

Adding column on table having existing records.

Let us create table customers2 and add 2 records in it.

create table customers2
(custid varchar(5),
 custname varchar(50)
 )
 Go
Insert into customers2 Values('C1','John')
Insert into customers2 Values('C2','Martin')
go

Scenario 2.a: Adding columns with default null values on existing records.

Alter Table customers2
Add DOB Date
go

Let us see the table Customers2

select * from customers2

sqlserver-nulls-new-column
Image2-No-Default-Value

Scenario 2.b: Adding columns with default value

It might happen that you want to add a column specifying some default value and there are existing records in the table. But in the default setup, those existing records will not get that value.
Let us add token amount as a column to this Customers2 table with default value as 100.

Alter Table customers2
Add Token_Amount Integer Default 100
go

Let us see the table Customers2

select * from customers2

sql-no-default-value
Image2-No-Default-Value

Note -The default value will be effective when new record is added.

Scenario 2.c: Adding columns with default value and applying those values on existing records.

Let us add another column tax with default value as 50. This value needs to be added to the existing records as well. We need to explicitly specify the clause With Values.


Alter Table customers2
Add Tax Integer Default 50 With Values
go

default-value-withclause
Image3-Default-Value-WithClause

Scenario 2.d: Adding columns with Not Null constraint on existing records.

Let us add a column Country with Not Null constraint. Now this will be contradictory because the earlier two records cannot have null value for Country.

Let us try.

Alter Table customers2
Add Country Varchar(50) Not Null
go

It will throw the following error.

ALTER TABLE only allows columns to be added that can contain nulls

To rectify this, we need to mention the default value which will safeguard the earlier records.

Scenario 2.e: Adding columns with not null constraint and default value on existing records

Let’s have India as the default value.

Alter Table customers2
Add Country Varchar(50) Default 'India' Not Null
go

Now this works fine. Let us see the table again.

select * from customers2

default-value-notnull
Image4-Default-Value-NotNull


Conclusion:

In this article, we have seen the following scenarios of using ALTER table to add column to a SQL Server table:

1. Adding one or multiple columns to an empty table.
2. Adding column to table having records. Those records get default value as null.
3. Adding column with default value to table having records
  • In absence of With Values clause
  • In presence of With Values clause
4. Adding column with not null constraint to table having records
  • In absence of Default clause
  • In presence of Default clause

T-SQL Date Format with Convert

In this article we are going to see a very handy SQL Server T-SQL function for formatting date called Convert(). It is generally used to show date values in different styles or formats.

Normally any date value when entered in a SQL Server table gets displayed in the default format as YYYY-MM-DD. Many a times, in your client application you may need to change the output of date & time for display purpose. For doing this the T-SQL Convert function is used.


Convert Function Example

Let us create a sample database Convert_Demo in our SQL Server database instance.

Use Master
go
Create Database Convert_Demo
go
Use Convert_Demo
go
CREATE TABLE EMP
       (EMPNO Integer Primary Key,
        ENAME Varchar(15),
        JOB Varchar(9),
        MGR Integer,
        HIREDATE date,
        SAL Integer,
        COMM Integer,
        DEPTNO Integer);
go
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902, '12/17/80',
        800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        '5/20/81',1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        '5/22/81', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        '4/2/81',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        '9/28/81', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        '5/1/81',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        '6/9/81',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        '12/9/82', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        '11/17/81', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        '9/8/1981',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        '1/12/83', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        '12/3/81',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        '12/3/81',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        '1/23/82', 1300, NULL, 10);
go

Now let us see the default presentation style of the hiredate column by executing the following SQL statement.

select ename, hiredate from emp

sql-default-date-format


Image1-Default-date-format


Syntax of Convert function

CONVERT(data_type(length),data_to_be_converted,style)

Data_type(length) - varchar data type particulary for displaying dates in different format.

data_to_be_converted - The date value which needs to be converted.

Style - There are some predefined style ids like 101 for American date format, 103 in British format and so on. The style chart has been shown next –

sql-date-formats

Now let us see the employee names & hiredate in American as well as British formats using T-SQL Convert function.

select ename, hiredate as [Default Date Format],
convert(varchar, hiredate, 101) as [American],
convert(varchar, hiredate, 103) as [British] 
from emp

sql-changed-date-formats
Image2-Changed-Date-Formats


Convert without StyleID:

Convert is also useful to convert from one data type to another – For example changing Float to Int, Number to Float or Number to Varchar.

Using Convert to change from Float to Int


SELECT CONVERT(int, 14.85)

sql-changing-float-to-int


Image3-Changing-Float-To-Int

Using Convert to change from Number to Float


SELECT CONVERT(float, 14.0989654)    

sql-number-to-float
Image4-Number-to-Float

Using Convert to change from Number to Varchar

SELECT CONVERT(varchar(4), '12.3456')

sql-number-to-varchar
Image5-Number-to-Varchar

Conclusion:

 
We have seen in this article that using T-SQL function Convert, we can display date in different formats and also can type cast one data type into another.