Как добавить новый столбец в таблицу между существующими столбцами?

Автор: Моисеенко С.И.
Источник: sql-ex.ru

Уже не впервые на форумах встречаю вопрос о том, как добавить новый столбец в определенное место существующей таблицы, скажем, между первым и вторым столбцом. Этот наивный с точки зрения реляционной модели вопрос, тем не менее, имеет некоторый смысл с точки зрения языка SQL.

Я говорю "наивный", поскольку по определению атрибуты отношения не упорядочены, и обращение к значениям атрибута выполняется по его имени, но не по позиции. Что же касается языка SQL, то столбцы в таблице имеют порядок, который задается в операторе CREATE TABLE. Новый же столбец, который добавляется с помощью оператора ALTER TABLE, становится последним в таблице. Т.е. в языке SQL нет возможности непосредственно добавить столбец в определенную позицию в списке столбцов.

Другой вопрос, а зачем это нужно? Мне приходит в голову такой вариант. Скажем, в клиентском приложении для генерации отчетов используется запрос типа

 SELECT * FROM EmployeesORDER BY last_name, first_name;

Если потребовалось добавить в таблицу Employees дополнительную информацию о сотрудниках, которая логически должна находиться в определенном месте (например, отчество непосредственно между именем и фамилией), то вместо того, чтобы вносить изменения в клиентские приложения, может оказаться проще изменить структуру таблицы Employees.

Итак, имеется таблица Employees, которая создается следующим оператором:

 CREATE TABLE Employees(

emp_num INT NOT NULL PRIMARY KEY,

first_name CHAR(30) NOT NULL,

last_name CHAR(30) NOT NULL);

Теперь нам требуется добавить столбец middle_name (отчество) между столбцами first_name и last_name.

Можно использовать такой алгоритм:

» создание новой таблицы требуемой структуры;
» копирование данных из таблицы Employees в эту новую таблицу;
» удаление таблицы Employees;
» переименование новой таблицы в таблицу с именем Employees.

Ниже приводятся операторы T-SQL, которые реализуют этот алгоритм.

 -- Создаем временную таблицу требуемой структуры 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 -- Копируем данные из старой таблицы в новую INSERT INTO Emp_temp(emp_num, first_name, last_name)SELECT * FROM Employees;GO -- Удаляем старую таблицу DROP TABLE Employees;GO -- Переименовываем новую EXEC sp_rename `Emp_temp`, `Employees`;GO 

Обратите внимание, что столбец middle_name допускает NULL-значения. Мы не можем добавить столбец в существующую таблицу (или, как в нашем случае, не задавая значения для этого столбца при копировании данных из таблицы Employees в таблицу Emp_temp), если он не имеет значения по умолчанию. Здесь мы принимаем по умолчанию значение NULL.

Мы можем выполнить два первых шага за одно действие с помощью оператора SELECT INTO, который "на лету" создает новую таблицу:

 SELECT emp_num, first_name, CAST(NULL AS CHAR(30)), last_nameINTO Emp_tempFROM Employees;

Оператор CAST позволяет нам тут же задать требуемый тип добавляемого столбца. Остальные столбцы наследуют типы из таблицы-источника.

Если вы хотите проверить работу последнего скрипта, приведите таблицу в исходное состояние, удалив добавленный ранее столбец:

 ALTER TABLE Employees DROP COLUMN middle_name;

Заметим, что при использовании оператора SELECT INTO теряются ключи. Поэтому нам придется добавить ограничение PRIMARY KEY (первичный ключ) либо во временную таблицу, либо уже в переименованную, чтобы получить в точности требуемую структуру:

 ALTER TABLE Emp_tempADD CONSTRAINT emp_PK PRIMARY KEY(emp_num);

Аналогичный алгоритм можно применить и для перестановки уже существующих столбцов. Помимо указанной причины такая перестановка может повысить производительность, связанную с сокращением объема данных, записываемых в журнал транзакций в некоторых реализациях. Это связано со спецификой обработки строк фиксированной и переменной длины. Вот какие рекомендации по этому поводу дает Джо Селко^*:

» помещайте первыми нечасто обновляемые столбцы постоянной длины;
» затем помещайте нечасто обновляемые столбцы переменной длины;
» последними помещайте часто обновляемые столбцы;
» ставьте рядом столбцы, которые, как правило, обновляются одновременно.

^*Селко Д. Стиль программирования Джо Селко на SQL. - М.: Изд-во "Русская редакция"; СПб.: Питер, 2006