Alter mssql table: SQL Server: ALTER TABLE Statement

Модификация типа данных столбца

MS SQL Server


В MS SQL Server для изменения типа данных столбцов используется
предложение ALTER COLUMN инструкции ALTER TABLE.

ALTER TABLE
<table_name>
ALTER COLUMN
<col_name>
<new_col_type> [ NULL | NOT NULL ]

В аргументе column_name содержит
имя столбца, подлежащего изменению. Аргумент new_col_type
содержит описание нового типа данных для изменяемого столбца.

Ниже приведены критерии для
аргумента new_col_type
изменяемого столбца:

• Предыдущие типы данных
  должны быть неявно преобразуемыми в новый тип данных.




• Аргумент new_col_type
  не может принадлежать к типу timestamp.




• Если изменяемый столбец
  является столбцом идентификаторов, то новый тип данных должен
  поддерживать свойство идентификатора.

Тип данных
столбцов text, ntext и image
может быть изменен только следующими способами:

• text
  на varchar(max), nvarchar(max) или xml




• ntext
  на varchar(max), nvarchar(max) или xml




• image
  в varbinary(max)

MySQL
Server


В СУБД MySQL описание столбца меняется с помощью предложений CHANGE или
MODIFY в операторе ALTER TABLE.

ALTER TABLE
<table_name> MODIFY <col_definition>


ALTER TABLE <table_name> CHANGE
<old_col_name> <col_definition>

В выражении
col_definition для MODIFY и CHANGE используется тот же синтаксис, что и
для CREATE TABLE. Следует учитывать, что этот синтаксис включает имя
столбца, а не просто его тип.

Операция
CHANGE, в отличие от операции MODIFY, не только меняет тип данных, но и
переименовывает столбец. Поэтому при ее использовании сначала
указывается имя столбца, который будет меняться, а затем дается
полностью новое объявление столбца, включая опять же его имя.

Как ИЗМЕНИТЬ сразу несколько столбцов в SQL Server

Если вы вносите изменения в Management Studio и генерируете сценарии, она создает новую таблицу и вставляет в нее старые данные с измененными типами данных. Вот небольшой пример изменения типа данных двух столбцов

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.tblDiary
    DROP CONSTRAINT FK_tblDiary_tblDiary_events
GO
ALTER TABLE dbo.tblDiary_events SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tblDiary
    (
    Diary_ID int NOT NULL IDENTITY (1, 1),
    Date date NOT NULL,
    Diary_event_type_ID int NOT NULL,
    Notes varchar(MAX) NULL,
    Expected_call_volumes real NULL,
    Expected_duration real NULL,
    Skill_affected smallint NULL
    )  ON T3_Data_2
     TEXTIMAGE_ON T3_Data_2
GO
ALTER TABLE dbo.Tmp_tblDiary SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary ON
GO
IF EXISTS(SELECT * FROM dbo.tblDiary)
     EXEC('INSERT INTO dbo.Tmp_tblDiary (Diary_ID, Date, Diary_event_type_ID, Notes, Expected_call_volumes, Expected_duration, Skill_affected)
        SELECT Diary_ID, Date, Diary_event_type_ID, CONVERT(varchar(MAX), Notes), Expected_call_volumes, Expected_duration, CONVERT(smallint, Skill_affected) FROM dbo.tblDiary WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_tblDiary OFF
GO
DROP TABLE dbo.tblDiary
GO
EXECUTE sp_rename N'dbo.Tmp_tblDiary', N'tblDiary', 'OBJECT' 
GO
ALTER TABLE dbo.tblDiary ADD CONSTRAINT
    PK_tblDiary PRIMARY KEY NONCLUSTERED 
    (
    Diary_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2

GO
CREATE UNIQUE CLUSTERED INDEX tblDiary_ID ON dbo.tblDiary
    (
    Diary_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
CREATE NONCLUSTERED INDEX tblDiary_date ON dbo.tblDiary
    (
    Date
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 86, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON T3_Data_2
GO
ALTER TABLE dbo.tblDiary WITH NOCHECK ADD CONSTRAINT
    FK_tblDiary_tblDiary_events FOREIGN KEY
    (
    Diary_event_type_ID
    ) REFERENCES dbo.tblDiary_events
    (
    Diary_event_ID
    ) ON UPDATE  CASCADE 
     ON DELETE  CASCADE 

GO
COMMIT

SQL ALTER TABLE — изменение таблицы базы данных

Команда языка SQL ALTER TABLE предназначена для изменения структуры столбца таблицы базы данных, если
таблица уже существует.

Запрос с командой ALTER TABLE на добавление нового столбца в таблицу имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ADD ИМЯ_НОВОГО_СТОЛБЦА ТИП_ДАННЫХ

Пример 1. Есть база данных фирмы — Company. В ней есть таблица
Employee (Сотрудник). Требуется добавить в эту таблицу новый столбец — SEX (Пол). Соответствующий запрос
выглядит следующим образом:

ALTER TABLE EMPLOYEE
ADD SEX CHAR(1)

Таким образом в таблице появился новый столбец, значения которого могут принимать
символьный тип и состоять из одного символа.

Пусть теперь требуется установить значение записей в таблице по умолчанию. Запрос с командой ALTER TABLE
для этой цели содержит ещё одну команду — ALTER COLUMN — и имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ALTER COLUMN ИМЯ_СТОЛБЦА
SET DEFAULT ‘ЗНАЧЕНИЕ_ПО_УМОЛЧАНИЮ’

Пример 2. Пусть в фирме, база данных которой имеет название Company,
трудятся в основном женщины. Администратор базы данных решил сократить процесс заполнения данными столбца
SEX (Пол) и установить его значение по умолчанию — ‘F’ (что означает женский пол). Соответствующий запрос
выглядит следующим образом:

ALTER TABLE EMPLOYEE
ALTER COLUMN SEX
SET DEFAULT ‘F’

Теперь только если новый сотрудник — мужчина, потребуется вписывать в столбец SEX
значение ‘M’, а в большинстве случаев значение установится по умолчанию — ‘F’.

При помощи запроса с командой ALTER TABLE можно и удалить столбец из таблицы. Синтаксис команды будет
следующим:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
DROP COLUMN ИМЯ_СТОЛБЦА

Пример 3. Пусть в той же базе данных Company из
таблицы Employee требуется удалить столбец SEX (Пол). Соответствующий запрос
выглядит следующим образом:

ALTER TABLE EMPLOYEE
DROP COLUMN SEX

Таким образом в таблице появился новый столбец, значения которого могут принимать
символьный тип и состоять из одного символа.

О ключах в таблицах базы данных — на уроке Реляционная модель данных.
Если при создании таблицы базы данных не был определён первичный ключ (PRIMARY KEY), то это может быть
сделано при помощи команды ALTER TABLE. Синтаксис команды в случае простого первичного ключа следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА)

Синтаксис команды в случае составного первичного ключа:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА_1, ИМЯ_ИДЕНТИФИКАТОРА_2)

Пример 4. Работаем всё с той же базой данных Company,
со столбцом EMPLOYEE. Мы можем добавить простой первичный ключ этого столбца в виде идентификатора
сотрудника и сделать это так:

ALTER TABLE EMPLOYEE
ADD PRIMARY KEY (EMP_ID)

Если решено использовать составной ключ, состоящий из идентификатора сотрудника и
идентификатора отдела, в котором сотрудник работает, то используем следующий запрос:

ALTER TABLE EMPLOYEE
ADD PRIMARY KEY (EMP_ID, DEPT_ID)

О том, что такое ограничения целостности, говорилось на уроке Реляционная модель данных.
Если коротко — при удалении какой-либо строки из одной таблицы (1), в другой таблице (2), строка в которой
ссылается на строку таблицы (1), не должно быть ссылок, которые не содержатся в таблице (1), то есть
ссылок, ведущих в никуда.

Команда с выражением ALTER TABLE может понадобиться для установки значения NULL для такой ссылки. Её
синтаксис следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ
ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА
FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА)
REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ
ON DELETE SET NULL

Пример 5. Пусть в той же базе данных Company
требуется установить ссылочное ограничение: при удалении какого-либо отдела из таблицы Dept
в таблице Employee, содержатся ссылки на эти отделы, значение ссылки (внешнего ключа) должно быть
уставлено в NULL (полностью неопределенное). Соответствующий запрос
выглядит следующим образом:

ALTER TABLE EMPLOYEE
ADD CONSTRAINT DEPT_ID
FOREIGN KEY (DEPT_ID)
REFERENCES DEPT
ON DELETE SET NULL

Таким образом, если в таблице Employee есть сотрудник, работающий в отделе, который
вдруг был ликвидирован и запись о нём удалена из таблицы Dept, то в записи об этом сотруднике значение
столбца DEPT_ID примет значение NULL.

Запросом с командой ALTER TABLE можно также установить запрет на удаление ссылающихся записей из таблицы (1), если
существует хотя бы одна запись в таблице (2), на которую ссылается запись в таблице (1). Синтаксис такого
запроса следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ
ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА
FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА)
REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ
ON DELETE RESTRICT

Пример 6. Далее работаем с базой данных сети аптек, которую мы создали на уроке Создание базы
данных SQL и таблиц базы данных.

Следующая команда с выражением alter table устанавливает запрет на удаление группы препаратов из таблицы Group, если существует хотя бы один препарат этой группы, что определяется записью в таблице Preparation:

alter table Preparation add constraint GR_ID foreign key(GR_ID) references Group on delete restrict

Устанавливаем запрет на удаление аптеки из таблицы Pharmacy, если существует хотя бы один сотрудник этой аптеки, что определяется записью в таблице Employee:

alter table Employee add constraint PH_ID foreign key(PH_ID) references
Pharmacy on delete restrict

Запросом с командой ALTER TABLE можно также определить, что если удалить запись в таблице (2), на которую
ссылается запись в таблице (1), то должны быть удалены все соответствующие записи в таблице (1) (каскадное
удаление).
Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ
ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА
FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА)
REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ
ON DELETE CASCADE

Пример 7. Продолжаем работать с базой данных сети аптек.

Вновь изменяем таблицу AVAILABILITY и определяем, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице AVAILABILITY.

alter table AVAILABILITY add constraint PR_ID foreign key(PR_ID)
references PREPARATION on delete cascade

Теперь определим, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице AVAILABILITY:

alter table AVAILABILITY add constraint PH_ID foreign key(PH_ID)
references PHARMACY on delete cascade

Следующая команда модифицирует таблицу DEFICIT и определяет, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице DEFICIT:

alter table DEFICIT add constraint PR_ID foreign key(PR_ID) references
PREPARATION on delete cascade

iv>

Вновь модифицируем таблицу DEFICIT и определяем, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице DEFICIT:

alter table DEFICIT add constraint PH_ID foreign key(PH_ID) references
PHARMACY on delete cascade

Запросом с командой ALTER TABLE можно установить проверочное ограничение.
Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ADD CONSTRAINT CHECK_DATA
CHECK(ПРОВЕРЯЕМОЕ_УСЛОВИЕ)

Пример 8. Продолжаем работать с базой данных сети аптек.

Нужно модифицировать таблицу AVAILABILITY и определить, что в одной и той же записи значение атрибута DateStart таблицы не должно быть меньше значения DateEnd.

ALTER TABLE AVAILABILITY ADD CONSTRAINT CHECK_DATA CHECK(DateStart

Изменяем таблицу DEFICIT и определяем, что в одной и той же записи значение атрибута DateStart таблицы не должено быть меньше значения DateEnd:

ALTER TABLE DEFICIT ADD CONSTRAINT CHECK_DATA CHECK(DateStart

Поделиться с друзьями

Реляционные базы данных и язык SQL

Инструкция ALTER TABLE в SQL Server (продолжение)

CHECK

Производится проверка того, что значение, вставляемое в указанный столбец таблицы, является допустимым с точки зрения проверочного выражения. Например, ниже приведен пример таблицы с двумя ограничениями CHECK уровня столбца.

CREATE TABLE people
(people_id CHAR(4) CONSTRAINT Pk_dist_id PRIMARY KEY CLUSTERED
CONSTRAINT ck_dist_id CHECK (dist_id LIKE ' [A-Z] [A-Z] [A-Z] [A-Z]'),
people_name VARCHAR(40) NULL,
people_addr1 VARCHAR(40) NULL,
people_addr2 VARCHAR(40) NULL,
city VARCHAR(20) NULL,
state CHAR(2) NULL CONSTRAINT def_st DEFAULT ('CA') CONSTRAINT ck_st REFERENCES states(state_ID),
zip CHAR(5) NULL CONSTRAINT ck_dist_zip CHECK(zip LIKE '[0-9] [0-9] [0-9] [0-9] [0-9]'),
phone CHAR(12) NULL,
sales_rep empid NOT NULL DEFAULT USER)
GO

Проверочное ограничение на значение people_id проверяет то, что идентификатор содержит только буквы, а ограничение на значение zip удостоверяется в том, что значение содержит только цифры. Ограничение REFERENCE на значение state производит поиск в таблице states. Ограничение REFERENCE по сути аналогично ограничению CHECK, за исключением того, что оно генерирует список допустимых значений по значениям в другом столбце. Этот пример также иллюстрирует, каким образом ограничениям присваиваются имена с помощью синтаксиса CONSTRAINT имя_ограничения.

COLLATE

Позволяет программистам изменять используемый порядок сортировки и набор символов в отдельных столбцах.

TEXTIMAGEJDN {файловая группа DEFAULT)

Управляет размещением столбцов text, ntext и image, позволяя помещать данные LOB в выбранную файловую группу. Если предложение опущено, эти столбцы сохраняются в файловой группе по умолчанию, там же, где все прочие таблицы и объекты базы данных.

WITH [NO]CHECK

Определяет, будут ли данные в таблице проверяться с использованием добавляемых позже ключей и ограничений. Если ограничения добавляются с опцией WITH NOCHECK, то оптимизатор запросов игнорирует их до тех пор, пока они не будут включены с помощью команды ALTER TABLE имя_таблицы CHECK CONSTRAINT ALL. Если ограничения добавляются с опцией WITH CHECK, то проверка всех данных, уже имеющихся в таблице, производится немедленно.

[NOJCHECK CONSTRAINT

Существующее ограничение включается (CHECK CONSTRAINT) или выключается (NO CHECK CONSTRAINT).

{ENABLE] DISABLE) TRIGGER {ALL имя_триггера [, …]}

Предложение включает или соответственно отключает указанный триггер или триггеры. Все триггеры в таблице можно включить или выключить, заменив ключевое слово ALL на имя таблицы: ALTER TABLE employee DISABLE TRIGGER ALL. Вы также можете включить или отключить один триггер с именем имя_триггера или несколько триггеров, перечислив их имена через запятую.

SQL Server позволяет присвоить имя любому ограничению уровня столбца при помощи конструкции CONSTRAINT имя ограничения… (далее идет текст ограничения). В одном столбце можно использовать несколько ограничений, если только они не являются взаимоисключающими (например, PRIMARY KEY vl NULL).

SQL Server также позволяет создавать локальные временные таблицы, но при этом не используется синтаксис ANSI. Для локальной временной таблицы, которая сохраняется в базе данных tempdb, требуется префикс в виде одного знака диеза (#) перед именем таблицы. Локальную временную таблицу может использовать пользователь или процесс, создавший ее, и она удаляется, когда пользователь завершает соединение или когда процесс завершает работу. Глобальная временная таблица, которую могут использовать все пользователи и процессы, подключенные к базе данных, можно создать, используя в качестве префикса перед именем таблицы двойной знак диеза (##). Глобальная таблица удаляется, когда завершается работа создавшего его процесса или разрывается соединение с создавшим ее пользователем.

Система SQL Server также позволяет создавать таблицы со столбцами, которые содержат вычисляемое значение. Такой столбец не содержит реальных данных. Это виртуальный столбец, содержащий выражение, использующее другие столбцы, входящие в таблицу. Например, вычисляемый столбец может содержать такое выражение, как стоимость_заказа AS (цена * количество). Вычисляемые столбцы также могут содержать константы, функции, переменные, невычисляемые столбцы, которые связаны друг с другом при помощи операторов.

Любые ограничения уровня столбца, которые приведены выше, могут также быть объявлены на уровне таблицы. Иными словами, ограничения PRIMARY KEY, CHECK и прочие можно объявлять после объявления всех столбцов в инструкции CREATE TABLE. Это очень полезная возможность для тех ограничений, которые касаются нескольких столбцов. Например, ограничение UNIQUE уровня столбца может применяться только к этому столбцу. Однако, если ограничение объявлено на уровне таблицы, это позволяет распространять его действие на несколько столбцов. Ниже приводится пример ограничений уровня столбца и уровня таблицы.

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

В следующем примере к таблице добавляется новое ограничение CHECK, но не производится проверка того, что существующие в таблице значения удовлетворяют этому ограничению.

ALTER TABLE favorite_book WITH NOCHECK ADD CONSTRAINT extra_check CHECK (ISBN > 1) GO

В следующем примере мы добавляем столбец с присвоенным значением по умолчанию (DEFAULT), который добавляется в каждую существующую в таблице строку.

ALTER TABLE favorite_book
ADD reprint_nbr INT NULL CONSTRAINT add_reprint_nbr
DEFAULT 1 WITH VALUES GO

Теперь отключаем ограничение.

ALTER TABLE favorite_book NOCHECK CONSTRAINT add_reprint_nbr GO

CREATE SCHEMA DROP

Триггер — это особый тип записанной процедуры, которая автоматически срабатывает (отсюда и термин «триггер»), когда в таблице выполняется конкретная инструкция, связанная с модификацией данных. Триггер прямо связан с таблицей и считается зависимым объектом. Например, вам может понадобиться, чтобы все значения part_numbers в таблице sales обновлялись при изменении значения part_number в таблице products, обеспечивая таким образом синхронизацию.

SQL ALTER TABLE Заявление

SQL ALTER TABLE Заявление

Оператор ALTER TABLE используется для добавления, удаления или изменения столбцов в существующей таблице.

Оператор ALTER TABLE также используется для добавления и удаления различных ограничений на
существующая таблица.

ИЗМЕНИТЬ ТАБЛИЦУ — ДОБАВИТЬ столбец

Чтобы добавить столбец в таблицу, используйте следующий синтаксис:

ALTER TABLE имя_таблицы
ДОБАВИТЬ имя_столбца тип данных ;

Следующий SQL добавляет столбец «Электронная почта» в таблицу «Клиенты»:

ИЗМЕНЕНИЕ ТАБЛИЦЫ — ВЫПУСКНАЯ КОЛОНКА

Чтобы удалить столбец в таблице, используйте следующий синтаксис (обратите внимание, что некоторые
системы баз данных не позволяют удалить столбец):

ALTER TABLE имя_таблицы
DROP COLUMN имя_столбца ;

Следующий SQL удаляет столбец «Электронная почта» из таблицы «Клиенты»:

ИЗМЕНИТЬ ТАБЛИЦУ — ИЗМЕНИТЬ / ИЗМЕНИТЬ КОЛОНКУ

Чтобы изменить тип данных столбца в таблице, используйте следующий синтаксис:

SQL Server / MS Доступ:

ALTER TABLE имя_таблицы
ALTER COLUMN имя_столбца тип данных ;

Мой SQL / Oracle (предыдущая версия 10G):

ALTER TABLE имя_таблицы
ИЗМЕНИТЬ КОЛОНКУ имя_столбца тип данных ;

Oracle 10G и выше:

ALTER TABLE имя_таблицы
ИЗМЕНИТЬ имя_столбца тип данных ;

SQL ALTER TABLE, пример

Посмотрите на таблицу «Персоны»:

Теперь мы хотим добавить столбец с именем «DateOfBirth» в таблицу «Persons».

Мы используем следующий оператор SQL:

ИЗМЕНИТЬ ТАБЛИЦУ Лица
ДОБАВИТЬ DateOfBirth date;

Обратите внимание, что новый столбец «DateOfBirth» имеет тип date и будет содержать
Дата. Тип данных указывает, какой тип данных может содержать столбец. Для полного
ссылка на все типы данных, доступные в MS Access, MySQL и SQL Server,
перейдите к нашему полному справочнику по типам данных.

Таблица «Персоны» теперь будет выглядеть так:

Пример изменения типа данных

Теперь мы хотим изменить тип данных столбца с именем «DateOfBirth» в таблице «Persons».

Мы используем следующий оператор SQL:

ИЗМЕНИТЬ ТАБЛИЦУ Лица
ИЗМЕНИТЬ КОЛОНКУ Дата года рождения;

Обратите внимание, что столбец «DateOfBirth» теперь имеет тип года и будет содержать год в двух- или четырехзначном формате.

КАПЕЛЬНАЯ КОЛОНКА Пример

Затем мы хотим удалить столбец с именем «DateOfBirth» в таблице «Persons».

Мы используем следующий оператор SQL:

ИЗМЕНИТЬ ТАБЛИЦУ Лица
DROP COLUMN DateOfBirth;

Таблица «Персоны» теперь будет выглядеть так:

Изменение столбцов (ядро СУБД) — SQL Server

• 14.03.2017
• 2 минуты на чтение

В этой статье

Применимо к: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Параллельное хранилище данных

Вы можете изменить тип данных столбца в SQL Server с помощью SQL Server Management Studio или Transact-SQL.

Предупреждение

Изменение типа данных столбца, который уже содержит данные, может привести к безвозвратной потере данных при преобразовании существующих данных в новый тип. Кроме того, код и приложения, зависящие от измененного столбца, могут выйти из строя.К ним относятся запросы, представления, хранимые процедуры, пользовательские функции и клиентские приложения. Обратите внимание, что эти сбои будут каскадными. Например, хранимая процедура, которая вызывает определяемую пользователем функцию, которая зависит от измененного столбца, может завершиться ошибкой. Тщательно продумайте любые изменения, которые вы хотите внести в столбец, прежде чем вносить его.

В этой теме

Перед тем, как начать

Безопасность

Разрешения

Требуется разрешение ALTER для таблицы.

Использование SQL Server Management Studio

Чтобы изменить тип данных столбца

1. В обозревателе объектов щелкните правой кнопкой мыши таблицу со столбцами, для которых нужно изменить масштаб, и выберите Дизайн .

2. Выберите столбец, для которого вы хотите изменить тип данных.

3. На вкладке Свойства столбца щелкните ячейку сетки для свойства Тип данных и выберите новый тип данных из раскрывающегося списка.

4. В меню Файл щелкните Сохранить имя таблицы .

Примечание

Когда вы изменяете тип данных столбца, конструктор таблиц применяет длину по умолчанию для выбранного вами типа данных, даже если вы уже указали другой. Всегда устанавливайте длину типа данных для желаемого значения после указания типа данных.

Предупреждение

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

Использование Transact-SQL

Чтобы изменить тип данных столбца

1. В обозревателе объектов подключитесь к экземпляру компонента Database Engine.

2. На стандартной панели щелкните Новый запрос .

3. Скопируйте и вставьте следующий пример в окно запроса и нажмите Выполнить .

     СОЗДАТЬ ТАБЛИЦУ dbo.doc_exy (column_a INT);
   ИДТИ
   ВСТАВИТЬ В dbo.doc_exy (column_a) VALUES (10);
   ИДТИ
   ИЗМЕНИТЬ ТАБЛИЦУ dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
   ИДТИ
   
     

Дополнительные сведения см. В разделе ALTER TABLE (Transact-SQL)

.

SQL Server ALTER TABLE ADD Обзор столбца

В этой статье мы рассмотрим операторы SQL Server ALTER TABLE ADD Column для добавления столбцов в существующую таблицу. Мы также поймем влияние добавления столбца со значением по умолчанию и добавления и обновления столбца со значением позже в более крупных таблицах.

Как разработчику базы данных, вам также необходимо добавлять столбцы в существующие таблицы. Вы могли бы подумать, что добавление столбца в таблицу базы данных SQL Server не будет большой проблемой. Иногда вы можете добавлять столбец из самой SQL Server Management Studio. Что ж, это было бы хорошо для небольшой таблицы или таблицы, в которой нет большого количества транзакций. Давайте посмотрим, как добавить столбцы в существующую таблицу, а также разберемся с причинами и передовыми методами добавления столбца в таблицу большого размера.

Образец таблицы

Давайте создадим образец таблицы с достаточным набором данных из следующего скрипта.

Приведенный выше сценарий создаст образец таблицы с именем SampleTable .Поля данных добавлены, так что будет создана таблица большого размера. Затем было добавлено большое количество записей путем многократного выполнения следующего запроса.

После выполнения вышеуказанного запроса 500 000 записей обновляются до SampleTable .После выполнения вышеуказанного запроса следует размер таблицы и другие параметры для таблицы.

Это можно получить с помощью процедуры sp_spaceused ‘SampleTable’.

Ниже указан размер базы данных для данных и файла журнала.

Давайте быстро рассмотрим синтаксис добавления одного столбца в существующую таблицу с помощью оператора ALTER TABLE ADD, как показано ниже.

Вы можете использовать приведенный ниже оператор, чтобы добавить столбец NewColumn1 в нашу таблицу SampleTable.

Кроме того, вы можете добавить несколько столбцов в таблицу, используя один оператор SQL Server ALTER TABLE, как показано ниже.

При добавлении столбца в большую таблицу обычно требуется добавить столбец и заполнить добавленный столбец значением по умолчанию.Например, если вы хотите добавить столбец статуса, следующий шаг — заполнить столбец значением по умолчанию.

Добавление столбца с ограничениями по умолчанию

Для достижения указанной выше цели проще всего добавить столбец с ограничением по умолчанию. При добавлении столбца по умолчанию в таблицу будет добавлено значение по умолчанию. Следующий скрипт использует оператор SQL Server ALTER TABLE ADD Column (в нашем случае статус) для добавления столбца с именем Status с ограничением по умолчанию.

Когда столбец добавлен, как указано выше, столбец состояния будет добавлен со значением INC для всех записей.

Из профилировщика следующие статические данные собираются через SQL Profiler во время добавления столбца со значениями по умолчанию.

Вы можете видеть, что столбец добавляется в таблицу менее чем за одну секунду, а операция требует минимальных затрат.

Ниже приводится статистика блокировок в столбце, добавленная с ограничением.

Обратите внимание, что следующий запрос должен быть выполнен в открытой транзакции, чтобы получить приведенную выше статистику блокировок.

Это показывает, что таблица (объект) имеет намеренную монопольную блокировку, что означает, что таблица не блокируется исключительно во время добавления столбца.Кроме того, добавление столбца со значением по умолчанию не заняло ни минуты, хотя в нем 500 000 записей.

Посмотрим на размер таблицы.

Вы увидите, что ничего не изменилось.

Давайте посмотрим размеры файлов базы данных.

В файле данных, как и в файле журнала, ничего особенного не изменилось. Все эти результаты показывают, что добавление столбца с ограничением по умолчанию приведет только к изменению метаданных.

Обновление со значением

Давайте обновим тот же столбец другим значением и получим ту же статистику.

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

Посмотрим на размер стола.

Таблица увеличилась на некоторое значение, как показано на рисунке выше.

Ниже приведены подробные сведения о затратах на запросы, полученные из профилировщика SQL.

Очевидно, что при обновлении столбца для большой таблицы потребление ресурсов велико.

Ниже приведены размеры файлов базы данных при добавлении столбца и обновлении значений.

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

В чем разница в этих сценариях? До SQL Server 2012 при добавлении столбца со значением по умолчанию будет такое же поведение.В SQL Server 2012 и более поздних версиях такой ситуации больше нет, столбец добавляется в таблицу в интерактивном режиме, и обновление не происходит, это только изменение метаданных.

Как это достигается

Это достигается довольно простым, но новым подходом. sys.system_internals_partition_columns DMV имеет два дополнительных столбца с именами has_default и default_value , как показано ниже.

Поэтому, когда столбец добавлен со значением по умолчанию, он не будет обновлять страницу данных, вместо этого он обновит эту системную таблицу.При обновлении строки значение по умолчанию будет перенесено в таблицу, даже если столбец значений по умолчанию не обновлен.

Теперь следующий вопрос: что, если ограничение по умолчанию будет отброшено сразу после его создания. В этом сценарии по-прежнему будет преобладать вышеуказанный параметр, поэтому будет сохранено ранее установленное значение по умолчанию.

Заключение

В этой статье мы рассмотрели базовый синтаксис SQL Server ALTER TABLE и реализовали его для добавления столбцов в существующую таблицу.

До SQL Server 2012 не было разницы между добавлением столбца со значением по умолчанию и добавлением столбца и его обновлением. Поэтому в предыдущую эпоху SQL Server 2012 вместо добавления столбца с ограничением по умолчанию лучше добавить столбец и обновлять его пакетами, чтобы таблица не была монопольно заблокирована.

Однако, начиная с SQL Server 2012, подход изменился, так что добавление столбца с ограничениями по умолчанию стало намного лучше.

Динеш Асанка является MVP в категории SQL Server за последние 8 лет. Он работает с SQL Server более 15 лет, пишет статьи и является соавтором книг. Он является докладчиком в различных группах пользователей и университетах. Он всегда готов учиться и делиться своими знаниями.

Посмотреть все сообщения Динеш Асанка

Последние сообщения Динеш Асанка (посмотреть все)

SQL Server ALTER TABLE REBUILD

Привет, друзья,

Я вижу обычную практику с администраторами баз данных: если вам нужно перестроить HEAP, вы создадите кластерный индекс и отбросите его — этот двухэтапный процесс выполняет работу по перестройке, но стоит дорого.SQL Server поддерживает команду ALTER TABLE REBUILD для восстановления кучи.

Создание отбрасываемого кластерного индекса, старый метод, является дорогостоящим, поскольку он должен обновить все некластеризованные индексы с правильным указателем дважды! Когда вы создаете кластеризованный индекс, он обновляет все некластеризованные индексы указателем на ключ кластеризации. Когда вы отбрасываете кластерный индекс, он обновляет все некластеризованные индексы указателем на RID (HEAP). Таким образом, в этом методе некластеризованные индексы перестраиваются дважды.

Вместо этого используйте команду SQL Server ALTER TABLE REBUILD, и некластеризованные индексы будут перестроены только один раз.

Вот быстрый пример, демонстрирующий разницу в производительности:

Я создаю таблицу и несколько некластеризованных индексов:

 используйте AdventureWorks2012
идти

- drop table person.persontemp

выберите * в Person.PersonTemp
от Person.Person

СОЗДАТЬ НЕЗАКЛЮЧЕННЫЙ ИНДЕКС [IX_Person_LastName_FirstName_MiddleName] НА [Person].[PersonTemp]
(
    [LastName] ASC,
    [Имя] ASC,
    [MiddleName] ASC
    )

СОЗДАТЬ НЕКЛАССНЫЙ ИНДЕКС [IX_EP] НА [Person]. [PersonTemp]
(
    [EmailPromotion] ASC
    )

СОЗДАТЬ НЕКЛАССНЫЙ ИНДЕКС [IX_PT] НА [Person]. [PersonTemp]
(
    [PersonType] ASC
    )

Теперь я включу ВРЕМЯ СТАТИСТИКИ и запишу время выполнения для техники CREATE / DROP Clustered Index и для команды ALTER TABLE REBUILD.установить время статистики на

СОЗДАТЬ КЛАСТЕРИРОВАННЫЙ ИНДЕКС [IX_BEID] ДЛЯ [Person]. [PersonTemp]
(
    [BusinessEntityID] ASC
    )

удалить индекс [IX_BEID] на [Person]. [PersonTemp]

Теперь посмотрим на время выполнения команды ALTER TABLE REBUILD:

установить время статистики на

ALTER TABLE [Person]. [PersonTemp] REBUILD 

Разница во времени выполнения очень очевидна. Особенно обратите внимание на процессорное время в обоих случаях.

ALTER TABLE REBUILD был введен в SQL Server 2008 (если я не ошибаюсь) и до сих пор не известен многим, поэтому рассмотрите возможность его использования в случае, если вам нужно перестроить кучу, а не использовать старую технику.

Кучу есть? Почему не кластерный индекс? Зачем тебе КУЧА? И прочее, прочее и прочее — это совсем другая история!

Но зачем вам перестраивать КУЧУ?

Изменить тип данных столбцов без отбрасывания таблицы в SQL

Прежде всего, мы создадим таблицу с именем tblManager, используя следующий запрос.

1. Create table tblManager
2. (
3. ID int primary key identity,
4. Name nvarchar (50),
5. Gender nvarchar (50),
6. Salary nvarchar (50)
7. )

the

the

the

Тип данных столбца Salary — nvarchar.

Здесь в этой таблице есть столбцы ID, Name, Gender и Salary.

Теперь мы напишем SQL-запрос и вставим образцы данных в таблицу tblManager.

1. Вставить в значения tblManager (‘Shaili Dashora’, ‘Female’, ‘30000’)
2. Вставить в значения tblManager (‘Sourabh Somani’, ‘Male’, ‘40000’)
3. Вставить в значения tblManager (‘Bhumika Dashora ‘,’ Female ‘,’ 20000 ‘)
4. Вставить в значения tblManager (‘ Raj Jain ‘,’ Male ‘,’ 20000 ‘)
5. Вставить в значения tblManager (‘ Rajendra Dashora ‘,’ Male ‘,’ 40000 ‘ )

Теперь мы увидим, что данные таблицы выглядят так.

Теперь мы хотим написать запрос, чтобы перечислить общие зарплаты менеджеров, сгруппированных по полу.Итак, нам нужен такой вывод.

Итак, напишем для этого следующий запрос.

1. Выберите Gender, SUM (Salary) as Total
2. from tblManager
3. Group by Gender

Здесь имейте в виду, что тип данных столбца Salary равен nvarchar.

Попробуем выполнить это и посмотрим, что получится.

Здесь сообщение об ошибке говорит: « Тип данных операнда nvarchar недопустим для оператора суммы » Таким образом, мы не можем использовать столбец Salary с агрегатной функцией SUM, потому что его DataType — nvarchar.

Итак, нам нужно изменить DataType этого столбца с nvarchar на integer.

Шаг 1 : Теперь это можно сделать в обозревателе объектов. Щелкните правой кнопкой мыши таблицу tblManager, затем выберите параметр «Дизайн».

Обратите внимание, что типом данных столбца Salary является nvarchar.

Теперь давайте изменим его на int вроде этого.

Теперь сохраните и посмотрите, что произойдет.Мы получим подобное предупреждение.

В основном это говорит о том, что нам нужно отбросить и заново создать таблицу, но у нас есть некоторые данные в этой таблице, поэтому, если мы отбросим и воссоздадим таблицу, мы потеряем данные.

Итак, как нам устранить это предупреждение? В основном есть два пути.

Вариант 1 : Используйте запрос SQL, чтобы изменить столбец, как показано ниже.

Итак, вместо использования конструктора SQL Server мы можем просто использовать SQL-запрос.

1. Изменить таблицу tblManager
2. Изменить столбец Зарплата int

Теперь после выполнения этого запроса мы выполним предыдущий запрос, чтобы перечислить общие зарплаты менеджеров, сгруппированных по полу, как это:

1. Выберите пол, СУММ (зарплата ) Как Всего
2. из tblManager
3. Группа по полу

Теперь посмотрим, как это работает.

Итак, это один из вариантов использования SQL-запроса.

Параметр 2 : Отключите параметр « Предотвратить сохранение изменений, требующих повторного создания таблицы » в SQLServer, этот параметр включен по умолчанию в SQL Server, поэтому нам нужно отключить этот параметр.

Для этого перейдите в SQL Server и в меню Tools выберите Options.

Теперь в окне параметров разверните Конструкторы и под этим «Конструкторы таблиц и баз данных» и снимите флажок «Предотвратить сохранение изменений, требующих повторного создания таблицы», затем нажмите OK.

Теперь мы откроем конструкторское представление таблицы, и здесь DataType столбца Salary имеет значение int.

Теперь, возможно, мы изменим его DataType int на десятичный (8,2) следующим образом.

Теперь мы сохраним изменения и увидим предупреждение, но ничего страшного, если мы нажмем «Да», изменения сохранены.

Теперь мы выполняем запрос и получаем такой же результат.

Спасибо.

Счастливого кодирования.

Как добавить столбец в SQL Server?

При работе с базой данных SQL Server вы можете изменить текущую таблицу и добавить в нее новый столбец без потери существующих данных. Оператор ALTER TABLE поможет.

Если вы используете оператор ALTER TABLE , он автоматически добавит новый столбец в конец таблицы. Для этого требуется привилегия ALTER:

  ALTER TABLE имя_таблицы ДОБАВИТЬ имя_столбца тип_данных NULL | НЕНУЛЕВОЙ;
  

Не удаляет существующие данные.

Например, вы можете добавить новый столбец final_score в таблицу class :

  ALTER TABLE класс
  ДОБАВИТЬ final_score INT NULL;
  

Вы также можете добавить несколько столбцов одновременно, используя оператор ALTER TABLE :

  ALTER TABLE имя_таблицы
  ДОБАВИТЬ col_name_1 data_type_1 NULL | НЕНУЛЕВОЙ,
      col_name_2 data_type_2 NULL | НЕНУЛЕВОЙ,
      ...
      col_name_n data_type_n NULL | НЕНУЛЕВОЙ;
  

Давайте посмотрим на пример, который показывает, как добавить несколько столбцов в таблицу в SQL Server:

  ALTER TABLE класс
  ДОБАВИТЬ last_name VARCHAR (50) NOT NULL,
      final_score INT NULL;
  

Использование средства графического интерфейса TablePlus

TablePlus поддерживает просмотр и редактирование структуры базы данных без написания запросов.

• Откройте базу данных с табличным представлением по умолчанию.
• Переключитесь в редактор структуры базы данных , щелкнув кнопку структуры внизу, или используйте сочетания клавиш Cmd + Ctrl +] .
• Щелкните + Столбец , чтобы создать новый столбец, или вы можете дважды щелкнуть пустую строку в представлении таблицы.
• Укажите определение столбца : имя, тип данных, возможность пустого значения, значение по умолчанию и внешний ключ.
• Нажмите Cmd + S , чтобы сохранить изменения на сервере. Вы также можете нажать на кнопку «Применить» в верхнем левом углу окна.

Нужен хороший клиент с графическим интерфейсом для работы с MS SQL Server? TablePlus предоставляет современный собственный инструмент с интуитивно понятным интерфейсом для управления несколькими базами данных, включая SQL Server, MySQL, PostgreSQL, SQLite, Oracle… И он доступен бесплатно!

Загрузить TablePlus для Mac .

Не на Mac? Загрузить TablePlus для Windows .

В Linux? Загрузить TablePlus для Linux

Требуется быстрое редактирование на ходу? Скачать TablePlus для iOS

Как изменить схему таблиц Mssql, хранимых процедур и представлений одновременно

Как изменить схему таблиц MS SQL, хранимых процедур и представлений одновременно

Иногда при изменении пользователей базы данных меняется владелец базы данных для таблицы из dbo.tablename в badschema.tablename. Это также повлияет на любые хранимые процедуры, созданные в таблицах со схемой incorect. Если вам нужно изменить схему для этих таблиц обратно на схему dbo по умолчанию, выполните следующие действия.

Для выполнения этих действий необходимо, чтобы на вашем компьютере была установлена ​​среда Microsoft SQL Server Management Studio. Если у вас нет этого установленного Microsoft SQL Server Mangement Studio Express 2008, его можно бесплатно загрузить в Microsoft, и его можно загрузить по этой ссылке: http: // www.microsoft.com/download/en/details.aspx?id=22985

Если вы собираетесь выполнять эти шаги, вы должны хорошо разбираться в MS SQL. Если вам это не нравится, обратитесь в службу поддержки за помощью. Перед внесением любых изменений в базу данных обязательно сделайте резервную копию своего сайта. Если вы являетесь клиентом виртуального хостинга, вы можете сделать это через панель управления, выполнив шаги, описанные здесь: Создайте резервную копию своего сайта с помощью Plesk. Если вы являетесь заказчиком выделенного сервера, вы можете создать резервную копию своего сайта либо через панель управления, либо напрямую через Control Suite, выполнив следующие действия: Как создать резервную копию домена с помощью Control Suite.
Часть 1

1. Откройте Microsoft SQL Server Management Studio и войдите в систему.
2. Нажмите кнопку Новый запрос .
3. Вставьте следующий сценарий в поле «Новый запрос», заменив oldschema на имя текущей схемы:

   SELECT 'ALTER SCHEMA dbo TRANSFER' + s. Имя + '.' + о. Имя

   ИЗ сис.Объекты o

   INNER JOIN sys.Schemas s on o.schema_id = s.schema_id

   ГДЕ с. Имя = 'oldschema'

   и (o.Type = 'U' or o.Type = 'P' or o.Type = 'V'

   )

4. Нажмите Выполнить

Результатом этого запроса будет вывод в поле «Результаты», который выглядит примерно так, как показано ниже:

ALTER SCHEMA dbo ПЕРЕДАВАЙТЕ свою схему.Стол1

ALTER SCHEMA dbo ПЕРЕДАЧА yourschema.Table2

ALTER SCHEMA dbo ПЕРЕДАЧА yourschema.Table3

ИЗМЕНЕНИЕ СХЕМА dbo ПЕРЕДАЧА вашей схемы.Таблица4

ALTER SCHEMA dbo ПЕРЕДАЧА yourschema.Table5

ALTER SCHEMA dbo ПЕРЕДАВАЙТЕ свою схему.Стол 6

Часть 2

1. Нажмите кнопку Новый запрос .
   

   Добавить комментарий Отменить ответ

   Ваш адрес email не будет опубликован. Обязательные поля помечены *

   Комментарий *

   Имя *

   Email *

   Сайт