How to add a new column into the table between the available columns?

It is not for the first time that on different forums I come across the problem of inserting new column into the definite position of the available table, say, between the first and the second column. This problem, naive as it may be in terms of relational model, is not altogether absurd nevertheless in terms of SQL language.

I call it "naive", because by definition the attributes of a relation are not arranged and the values of the attributes are processed according to their name but not to their position. As for SQL language, the columns of the table are ordered and their order is stipulated in CREATE TABLE statement. As for the new column, which is added using the ALTER TABLE statement, it is the last in the table. I.e. SQL standard does not presuppose the option to add the column into a certain position in the list of columns.

For the sake of truth it is necessary to tell that there are realizations of SQL language which extend the standard in this matter. For example, ALTER TABLE statement in MySQL can specify a position of an added column (the new column can become the first in the table or follow after the specified column).

Another question to ask is "What for is it really needed? It occurs to me that there can be such an option. Say, in client application for generation of the reports the query of the following type is used

SELECT * FROM Employees
ORDER BY last_name, first_name;

If one needs to add into Employees table any additional information on the employees, which in terms of logics should be put in a certain position (i.e., the patronymic comes directly between the name and the surname), it may prove easier to change the structure of the Employees table rather then to introduce amendments into the client application.

Thus, there is Employees table that is generated by the following statement:

CREATE TABLE Employees(
emp_num INT NOT NULL PRIMARY KEY,
first_name CHAR(30) NOT NULL,
last_name CHAR(30) NOT NULL
);

Now we need to add middle_name (patronymic) column between the first_name and last_name columns.

In MYSQL, we can do it easily enough:

ALTER TABLE Employees ADD COLUMN middle_name CHAR(10) NULL AFTER first_name;

SQL Server does not give us similar opportunity, but we could use the following algorithm::

» creation of new table that has got the required structure;
» copying of the data from Employees table into this new table;
» deleting of Employees table;
» renaming of the new table into the Employees table.

Hereafter we quote T-SQL statement, which uses this algorithm.

-- creating the new table with the structure required
CREATE TABLE Emp_temp(
emp_num INT NOT NULL PRIMARY KEY,
first_name CHAR(30) NOT NULL,
middle_name CHAR(30) NULL,
last_name CHAR(30) NOT NULL
);
GO
-- copying the data from Employees table into Emp_temp table
INSERT INTO Emp_temp(emp_num, first_name, last_name)
SELECT * FROM Employees;
GO
-- deleting the Employees table
DROP TABLE Employees;
GO
-- Renaming  Emp_temp table into Employees table
EXEC sp_rename 'Emp_temp', 'Employees';
GO

Pls. pay your attention to the fact, that middle_name column permits adding NULL values. We cannot add the column into existing table (or, which is the case, not supplying the value for this column when copying the data from Employees table into Emp_temp table), if it has no any default value. Here we assume NULL as default value.

We can complete two first steps in one operation using SELECT INTO statement, which will easily create the new table:

SELECT emp_num, first_name, CAST(NULL AS CHAR(30)), last_name
INTO Emp_temp
FROM Employees;

CAST statement enables us to set the required type of the added column. The other columns derive their types from the source table.

If you want to verify the operation of the last script, transform the table into initial one, deleting the column added earlier:

ALTER TABLE Employees DROP COLUMN middle_name;

It is worth mentioning that when using SELECT INTO statement keys will be lost. Therefore we need to introduce PRIMARY KEY constraint either into the temporary table or into the renamed one, in order to get the required structure:

ALTER TABLE Emp_temp
ADD CONSTRAINT emp_PK PRIMARY KEY(emp_num);

The analogous algorithm may be applied for exchange of the columns that are already available. Apart from the aforementioned ground such exchange may help to enhance performance due to reduction of the amount of data, registered in the transactions log in some realizations. It is caused to specifics of processing of rows of fixed and variable length. Here are recommendations by Joe Celko^* given in this connection:

» put first the updated columns of the fixed length;
» then put the less frequently updated columns of variable length;
» the last ones should be the frequently updated columns;
» put close to each other the columns, which, as a rule, are updated simultaneously.

^*Joe Celko. Joe Celko's SQL programming style. - Elsevier Inc., 2005

Dzone.com