Выражение, определяющее значение вычисляемого столбца. Вычисляемый столбец представляет собой виртуальный столбец, физически не хранящийся в таблице, если для него не установлен признак PERSISTED. Значение столбца вычисляется на основе выражения, использующего другие столбцы той же таблицы. Например, определение вычисляемого столбца может быть следующим: cost AS price * qty. Выражение может быть именем невычисляемого столбца, константой, функцией, переменной или любой их комбинацией, соединенной одним или несколькими операторами. Выражение не может быть вложенным запросом или содержать псевдонимы типов данных.
Компонент Database Engine автоматически определяет для вычисляемых столбцов допустимость значений NULL на основе использованных выражений. Считается, что результат большинства выражений допускает значение NULL, даже если используются только столбцы, для которых значение NULL запрещено, так как при возможном переполнении или потере точности может формироваться значение NULL.
ON { filegroup | "default" }
Указывает файловую группу, в которой хранится таблица. Если указан аргумент filegroup, таблица сохраняется в файловой группе с таким именем. Это должна быть существующая файловая группа в базе данных. Если указано значение "default" или параметр ON не определен вообще, таблица сохраняется в файловой группе по умолчанию. Механизм хранения таблицы, указанный в инструкции CREATE TABLE, изменить в дальнейшем невозможно.
[ type_schema_name. ] type_name
Указывает тип данных столбца и схему, к которой он принадлежит. Тип данных может быть одним из следующих.
· Системный тип данных.
· Пользовательский тип данных на основе системного типа данных SQL Server. Состояние признака NULL или NOT NULL для псевдонима типа данных может быть переопределено с помощью инструкции CREATE TABLE. Однако его длину изменить нельзя; длина типа данных-псевдонима не определяется инструкцией CREATE TABLE.
Precision
Точность указанного типа данных.
Scale
Масштаб указанного типа данных.
Max
Применяется только к типам данных varchar, nvarchar и varbinary для хранения 2^31 байт символьных и двоичных данных или 2^30 байт данных в Юникоде.
IDENTITY
Указывает, что новый столбец является столбцом идентификаторов. При добавлении в таблицу новой строки компонент Database Engine формирует для этого столбца уникальное последовательное значение. Свойство IDENTITY может быть установлено для столбцов типа tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0). Для каждой таблицы можно создать только один столбец идентификаторов. Необходимо указать оба параметра - начальное значение и приращение, или же не указывать ничего. Если ничего не указано, применяется значение по умолчанию (1,1).
· seed - значение, используемое для самой первой строки, загружаемой в таблицу.
· increment -значение приращения, добавляемое к значению идентификатора предыдущей загруженной строки.
COLLATE collation_name
Задает параметры сортировки для столбца. Имя параметров сортировки может быть либо именем параметров сортировки Windows, либо именем параметров сортировки SQL. Аргумент collation_name применим только к столбцам типов данных char, varchar, text, nchar, nvarchar и ntext. Если этот аргумент не указан, столбцу назначаются либо параметры сортировки определяемого пользователем типа, если столбец принадлежит к определяемому пользователем типу данных, либо установленные по умолчанию параметры сортировки для базы данных.
NULL | NOT NULL
Определяет, допустимы ли для столбца значения NULL. Параметр NULL не является ограничением в строгом смысле слова, но может быть указан так же, как и NOT NULL. Ограничение NOT NULL может быть указано для вычисляемых столбцов только в случае, если одновременно указан параметр PERSISTED.
Пример:
Следующий пример демонстрирует создание таблицы, содержащей пять столбцов, три из которых не допускают значения NULL и физически хранятся в базе данных.
Столбец MiddleName допускает пустые значения и физически хранится в базе данных, а столбец FullName является вычисляемым и не хранится в базе данных.
Столбец Id является столбцом идентификаторов. При добавлении в таблицу новой строки компонент Database Engine формирует для этого столбца уникальное последовательное значение, начиная с 1 и последовательно увеличивая значение на 1 для каждой новой строки.
CREATE TABLE Human
(
Id int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
MiddleName nvarchar(50) NULL,
FullName AS FirstName + ' ' + LastName
) ON [PRIMARY];
Изменение таблиц
Изменение определения таблицы путем изменения, добавления или удаления столбцов и осуществляется при помощи команды ALTER TABLE.
ALTER TABLE [ database_name. [ schema_name ]. | schema_name. ] table_name
{
ALTER COLUMN column_name
{
[ type_schema_name. ] type_name [ ({ precision [, scale ] | max }) ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
}
| ADD
{
<column_definition>
| <computed_column_definition>
} [,...n ]
| DROP
{
COLUMN column_name
}
}
database_name
Имя базы данных, в которой создана таблица.
schema_name
Имя схемы, которой принадлежит таблица.
table_name
Имя таблицы, подлежащей изменению. Если таблицы нет в текущей базе данных или она не содержится в схеме, которой владеет текущий пользователь, то и база данных, и схема должны быть явно указаны.
ALTER COLUMN
Указывает, что именованный столбец подлежит изменению.
column_name
Имя столбца, подлежащего изменению, добавлению или удалению. Аргумент column_name может содержать максимум 128 символов. Для новых столбцов аргумент column_name может быть опущен, если столбцы создаются с типом данных timestamp. Имя timestamp используется, если column_name не указано для столбца типа данных timestamp.
[ type_schema_name .] type_name
Новый тип данных для измененного столбца или тип данных для добавленного столбца. Тип данных для существующих столбцов секционированных таблиц изменить невозможно. Аргумент type_name может быть одним из следующих.
· Системным типом данных SQL Server.
· Псевдонимом типа данных, основанным на системном типе данных SQL Server. Прежде чем псевдонимы типов данных можно будет использовать в определении таблицы, их нужно создать с помощью инструкции CREATE TYPE.
· Определяемым пользователем типом.NET Framework и схемой, к которой он принадлежит. Перед использованием в определении таблицы определяемые пользователем типы.NET Framework создаются при помощи инструкции CREATE TYPE.
type_name изменяемого столбца должен отвечать следующим условиям:
· Предыдущие типы данных должны быть неявно преобразуемыми в новый тип данных.
· type_name не может иметь тип timestamp.
· По умолчанию для аргумента ANSI_NULL инструкции ALTER COLUMN всегда установлено значение ON; если не указано иное, столбец может содержать значения NULL.
· Аргумент заполнения ANSI_PADDING для инструкции ALTER COLUMN всегда принимает значение ON.
· Если изменяемый столбец является столбцом идентификаторов, то аргумент new_data_type должен иметь тип данных, который поддерживает свойство идентификатора.
· Текущая установка для аргумента SET ARITHABORT пропускается. Инструкция ALTER TABLE функционирует аналогично случаю, когда для аргумента ARITHABORT установлено значение ON.
precision
Точность указанного типа данных.
scale
Масштаб указанного типа данных.
Max
Применим только к типам данных varchar, nvarchar и varbinary для хранения 2^31-1 байт символов, двоичных данных и данных Юникода.
Если в столбце имеются данные, новый размер не может быть меньше максимального размера данных.
COLLATE < collation_name >
Задает новые параметры сортировки для изменяемого столбца. Если не указано, столбцу назначаются параметры сортировки, принятые в базе данных по умолчанию. Именем параметров сортировки может быть либо имя параметров сортировки Windows, либо имя параметров сортировки SQL.
Предложение COLLATE может быть использовано для изменения параметров сортировки только для столбцов типов char, varchar, nchar и nvarchar. Чтобы изменить параметры сортировки столбца пользовательского псевдонима типа данных, необходимо выполнить отдельные инструкции ALTER TABLE, чтобы изменить столбец на системный тип данных SQL Server, изменить параметры сортировки, а затем снова перевести столбец в псевдоним типа данных.
Инструкция ALTER COLUMN не может изменить параметры сортировки, если выполняется одно или несколько из следующих условий:
· Если на изменяемый столбец ссылается ограничение CHECK, ограничение FOREIGN KEY или вычисляемые столбцы.
· Если на базе столбца создан какой-нибудь индекс, статистика или полнотекстовый индекс. Статистика, автоматически созданная на базе изменяемого столбца, удаляется, если изменяются параметры сортировки столбца.
· Если связанное со схемой представление или функция ссылаются на столбец.
NULL | NOT NULL
Указывает, может ли столбец принимать значение NULL.
Столбцы, не допускающие значения NULL, могут быть добавлены инструкцией ALTER TABLE только в том случае, если для них указаны значения по умолчанию или если таблица пуста. Ограничение NOT NULL может быть указано для вычисляемых столбцов только в случае, если одновременно указан параметр PERSISTED. Если новый столбец допускает значения NULL, а значение по умолчанию не задано, то новый столбец содержит значение NULL для каждой строки в таблице. Если новый столбец допускает значение NULL, а определение по умолчанию добавляется с новым столбцом, то инструкция WITH VALUES может использоваться для хранения значений по умолчанию в новом столбце для каждой существующей строки в таблице.
Если новый столбец не допускает значение NULL и таблица не пуста, то определение DEFAULT должно быть добавлено с новым столбцом. Новый столбец автоматически загружается со значениями по умолчанию в каждой существующей строке нового столбца.
Значение NULL может указываться в инструкции ALTER COLUMN, чтобы принудить столбец NOT NULL допускать значения NULL. Значение NOT NULL может быть указано в инструкции ALTER COLUMN, только если столбец не содержит значения NULL.
Значения NULL следует обновить, присвоив некоторые значения, прежде чем разрешить инструкцию ALTER COLUMN NOT NULL, например:
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
При создании или изменении таблицы инструкцией CREATE TABLE или ALTER TABLE установки базы данных и сеанса изменяются и, возможно, переопределяют разрешение содержать значение NULL для типа данных, используемого в определении столбца. Рекомендуется всегда явно определять невычисляемые столбцы как NULL или NOT NULL.
Если добавляется столбец определяемого пользователем типа данных, то рекомендуется определить для этого столбца то же состояние допустимости значений NULL, что и в определяемом пользователем типе данных и задать для него значение по умолчанию.
ADD
Указывает, что добавляется одно или несколько определений столбца или определений вычисляемого столбца.
DROP { COLUMN column_name }
Указывает, что из таблицы должен быть удален столбец column_name. Может быть перечислено несколько столбцов.
Пример 1:
Следующий пример показывает добавление столбца, который допускает значения NULL. Если в таблицу уже были добавлены данные, то в новом столбце в каждой строке будет установлено значение NULL.
ALTER TABLE simpleTable ADD column_b VARCHAR(20) NULL;
Пример 2:
В следующем примере таблица изменяется путем удаления столбца.
ALTER TABLE simpleTable DROP COLUMN column_b;
Пример 3:
Следующий пример меняет столбец таблицы с INT на DECIMAL.
CREATE TABLE simpleTable (column_a INT);
GO
INSERT INTO simpleTable (column_a) VALUES (10);
GO
ALTER TABLE simpleTable ALTER COLUMN column_a DECIMAL (5, 2);
Удаление таблиц
Удаление таблиц производится командой DROP TABLE. Любое представление или хранимая процедура, ссылающаяся на удаленную таблицу, должна быть явно удалена с помощью инструкции DROP. Получить отчет о зависимостях в таблице можно с помощью sys.dm_sql_referencing_entities.
DROP TABLE [ database_name. [ schema_name ]. | schema_name. ] table_name [,...n ]
database_name
Имя базы данных, в которой создана таблица.
schema_name
Имя схемы, которой принадлежит таблица.
table_name
Имя таблицы, предназначенной для удаления.
Инструкцию DROP TABLE нельзя использовать для удаления таблицы, на которую ссылается ограничение FOREIGN KEY. Сначала следует удалить ссылающееся ограничение FOREIGN KEY или ссылающуюся таблицу. Если и ссылающаяся таблица, и таблица, содержащая первичный ключ, удаляются с помощью одной инструкции DROP TABLE, ссылающаяся таблица должна быть первой в списке.
Если удаляемая таблица ссылается на первичный ключ другой таблицы, которая также удаляется, ссылающаяся таблица с внешним ключом должна стоять в списке перед таблицей, содержащей указанный первичный ключ.
При удалении таблицы относящиеся к ней правила и значения по умолчанию теряют привязку, а любые связанные с таблицей ограничения или триггеры автоматически удаляются. Если таблица будет создана заново, нужно будет заново привязать все правила и значения по умолчанию, заново создать триггеры и добавить необходимые ограничения.
Большие таблицы и индексы из более чем 128 экстентов удаляются в два этапа: логический и физический. На логическом этапе существующие единицы распределения, используемые в таблице, отмечаются для освобождения и остаются заблокированными до фиксации транзакции. На физическом этапе страницы IAM, отмеченные для освобождения, физически удаляются пакетами.
Пример:
Следующий пример удаляет таблицу ProductVendor из текущей базы данных.
DROP TABLE simpleTable;Лекция № 13
