Using the ALTER TABLE Statement to Add a Column to a Table

Adding a column to a table is perhaps the most common use of the ALTER TABLE statement. The syntax of the ALTER TABLE statement to add a column is:

 ALTER TABLE 
 ADD   [DEFAULT ]
  [NOT NULL] [IDENTITY][UNIQUE][CHECK ()]
For example, to add a BADGE_NUMBER column to an EMPLOYEE table  defined by
 
 CREATE TABLE employee
 (employee_id            INTEGER,
  first_name             VARCHAR(20),
  last_name              VARCHAR(30),
  social_security_number CHAR(11),
  street_address         VARCHAR(35)
  PRIMARY KEY (employee_id))
 
you could use the ALTER TABLE statement:
 
 ALTER TABLE employee ADD badge_number INTEGER IDENTITY
 
The DBMS will add a new column, BADGE NUMBER, to the EMPLOYEE  table. In addition (MS-SQL Server only), IDENTITY characteristic in the current  example will have MS-SQL Server set the value of the BADGE_NUMBER column in each  row, starting with 1 and incrementing the value by 1 for each subsequent  row.
 
When you use the ALTER TABLE statement to add a new column to a  table, the DBMS adds the column to the end of the table's column definitions,  and it will appear as the rightmost column in subsequent queries. Unless you  specify a default value (or use the IDENTITY constraint on MS-SQL Server), the DBMS assumes NULL for the  value of the new column in existing rows.
 
Since the DBMS assumes NULL for the new column in existing rows,  you cannot simply add the NOT NULL constraint when you use the ALTER TABLE  statement to add a column. If you add the NOT NULL constraint, you must also  provide a default. After all, the DBMS assumes NULL for the new column in  existing rows if you don't provide a default value, and thus would immediately  violate the NOT NULL constraint.
 
When you add column to a table, the DBMS does not actually  expand existing rows. Instead, it expands only the description of the table to  include the new column(s) in the system tables. Each time you ask the DBMS to  read an existing row, it adds one (or more) NULL values for the new column(s)  before presenting query results. The DBMS will add the new column(s) to new rows  and to any existing rows as the DBMS stores updates to them.