Уже не впервые на форумах встречаю вопрос о том, как добавить новый столбец в определенное место существующей таблицы, скажем, между первым и вторым столбцом. Этот наивный с точки зрения реляционной модели вопрос, тем не менее, имеет некоторый смысл с точки зрения языка SQL.
Я говорю "наивный", поскольку по определению атрибуты отношения не упорядочены, и обращение к значениям атрибута выполняется по его имени, но не по позиции. Что же касается языка SQL, то столбцы в таблице имеют порядок, который задается в операторе CREATE TABLE. Новый же столбец, который добавляется с помощью оператора ALTER TABLE, становится последним в таблице. Т.е. стандарт языка SQL не предусматривает возможности непосредственно добавить столбец в определенную позицию в списке столбцов.
Справедливости ради следует сказать, что некоторые реализации языка SQL расширяют стандарт в этом плане. Например, в MySQL в операторе ALTER TABLE вы можете указать позицию добавляемого столбца (новый столбец может стать первым или после указанного столбца).
Другой вопрос, а зачем это нужно? Мне приходит в голову такой вариант. Скажем, в клиентском приложении для генерации отчетов используется запрос типа
SELECT * FROM Employees
ORDER 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.
В MySQL это можно сделать просто:
ALTER TABLE Employees ADD COLUMN middle_name CHAR(10) NULL AFTER first_name;
В SQL Server так поступить нельзя, но можно использовать следующий алгоритм:
» создание новой таблицы требуемой структуры; » копирование данных из таблицы 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_name
INTO Emp_temp
FROM Employees;
Оператор CAST позволяет нам тут же задать требуемый тип добавляемого столбца. Остальные столбцы наследуют типы из таблицы-источника.
Если вы хотите проверить работу последнего скрипта, приведите таблицу в исходное состояние, удалив добавленный ранее столбец:
ALTER TABLE Employees DROP COLUMN middle_name;
Заметим, что при использовании оператора SELECT INTO теряются ключи. Поэтому нам придется добавить ограничение PRIMARY KEY (первичный ключ) либо во временную таблицу, либо уже в переименованную, чтобы получить в точности требуемую структуру:
ALTER TABLE Emp_temp
ADD CONSTRAINT emp_PK PRIMARY KEY(emp_num);
Аналогичный алгоритм можно применить и для перестановки уже существующих столбцов. Помимо указанной причины такая перестановка может повысить производительность, связанную с сокращением объема данных, записываемых в журнал транзакций в некоторых реализациях. Это связано со спецификой обработки строк фиксированной и переменной длины. Вот какие рекомендации по этому поводу дает Джо Селко*:
» помещайте первыми нечасто обновляемые столбцы постоянной длины; » затем помещайте нечасто обновляемые столбцы переменной длины; » последними помещайте часто обновляемые столбцы; » ставьте рядом столбцы, которые, как правило, обновляются одновременно.
*Селко Д. Стиль программирования Джо Селко на SQL. - М.: Изд-во "Русская редакция"; СПб.: Питер, 2006
