Как добавить записи в таблицу sql server

2.16. Добавление записей

С выборкой данных мы разобрались и узнали все, что необходимо знать об операторе SELECT. Я надеюсь, что смог показать вам всю мощь выборки данных и теперь вы сможете самостоятельно писать запросы для решения своих задач.

Теперь давайте познакомимся с командами добавления данных в таблицу. Когда мы вначале главы заполняли таблицу данными, то вы должны были просто выполнить сценарий. Сейчас мы познакомимся с командой INSERT, которая добавляет в таблицу запись и вы сможете понять, как работал сценарий заполнения таблицы Chapter2/fill_data.sql.

Листинг 2.4. Общий вид команды INSERT

INSERT [ INTO] < table_name WITH ( < table_hint_limited >[ . n ] ) | view_name | rowset_function_limited > < [ ( column_list ) ] < VALUES ( < DEFAULT | NULL | expression >[ . n] ) | derived_table | execute_statement > >

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

INSERT INTO tbPeoples DEFAULT VALUES

Посмотрите, что добавил сервер в таблицу tbPeoples. Как? Нужно посмотреть последнюю строку, а этот запрос мы рассматривали в разделе 2.14. Напоминаю, как он выглядел:

SELECT * FROM tbPeoples WHERE idPeoples= (SELECT MAX(idPeoples) FROM tbPeoples) В результате вы должны увидеть следующую теблицу: idPeoples vcFamil vcName vcSurname idPosition dDateBirthDay ---------------------------------------------------------------- 20 NULL NULL NULL NULL NULL (1 row(s) affected)

В таблице tbPeoples первое поле ‘idPeoples’ является автоматически увеличиваемым, и новой строке это поле стало равным 20. Остальные поля не имеют значений по умолчанию и тут можно видеть только нулевые значения NULL.

Вернемся к запросу добавления записи и рассмотрим его подробнее. В данном запросе выполняется оператор INSERT INTO (вставить в), после которого указывается имя таблицы, в которую нужно вставить запись. Далее может идти описание имен полей, которые нужно заполнять или список значений. В нашем случае нет ни того, ни другого, зато есть DEFAULT VALUES (значения по умолчанию), который указывает на необходимость заполнить все поля значениями по умолчанию.

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

Следующий запрос пытается добавить запись с явно указанными данными:

INSERT INTO tbPeoples VALUES(122, 'ИВАНОВ', 'ИВАН', 'СЕРГЕЕВИЧ', 1, '01.01.1971')

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

В данном запросе указаны все поля и указаны в соответствии со всеми типами, но почему я сказал, что этот запрос «пытается добавить строку»? Ничего в голову не приходит? Первое поле имеет тип автоматического увеличиваемого числа. Это поле изменять нельзя, поэтому сервер вернет ошибку:

An explicit value for the identity column in table ‘tbPeoples’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

Явное значение для авто увеличиваемой колонки в таблице ‘tbPeoples’ может быть указано только когда используется список колонок и свойство IDENTITY_INSERT установлено в ON.

Неужели нельзя добавить строку? Конечно же, есть способ решения этой проблемы, просто необходимо явным образом задать список колонок, которые должны изменяться и установить им нужные значения.

Список колонок указывается в скобках после имени таблицы:

INSERT INTO tbPeoples (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) VALUES (122, 'ИВАНОВ', 'ИВАН', 'СЕРГЕЕВИЧ', 1, '01.01.1971')

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

Но этого не достаточно. Необходимо установить нужное свойство. Мы этого пока свойства не изменяли, но это делается с помощью оператора SET. Затем идет имя свойства (IDENTITY_INSERT), имя таблицы, свойство которой нужно изменить и напоследок – значение (чаще всего ON или OFF). В виде Transact-SQL команды это выглядит следующим образом:

SET IDENTITY_INSERT tbPeoples ON

Необходимо заметить, что изменение подобных свойств относится только к Transact-SQL и MS SQL Server.

Теперь посмотрим на полную SQL команду добавления записи с явным указанием значения для автоматически увеличиваемого значения:

SET IDENTITY_INSERT tbPeoples ON INSERT INTO tbPeoples (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) VALUES(122, 'ИВАНОВ', 'ИВАН', 'СЕРГЕЕВИЧ', 1, '01.01.1971')

После выполнения команды желательно вернуть значение параметра IDENTITY_INSERT в OFF:

SET IDENTITY_INSERT tbPeoples OFF

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

Как добавить строку, и при этом не указывать значение ключевого поля, чтобы сервер смог его установить самостоятельно? Очень просто. Дело в том, что перечислять все поля необязательно. Можно указать только те имена полей, значения которых вы хотите явно изменить. Например, в следующем запросе мы не указываем значение для ключевого поля «idPeoples»:

INSERT INTO tbPeoples (vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) VALUES('ИВАНОВ', 'ИВАН', 'АЛЕКСЕЕВИЧ', 1, '01.01.1971')

В списке полей и в списке VALUES не указывается значение для поля «idPeoples». Да и в списке значений после ключевого слова VALUES мы указываем значения, начиная с поля «vcFamil». Вместо этого, сервер самостоятельно добавит очередное значение точно так же, как когда мы добавляли строку из всех значений по умолчанию.

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

INSERT INTO tbPeoples (vcFamil) VALUES('ПЕТРОВ')

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

INSERT INTO tbPeoples (vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) VALUES('СЕРГЕЕВ', DEFAULT, NULL, 1, DEFAULT)

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

В перечислении можно указывать поля в любом порядке, и только те, которые необходимы. При этом в списке VALUES значения должны идти в том же порядке, в котором вы их перечисляли. Например, в следующем примере поля фамилии, имени и отчества заполняются в обратном порядке. При этом между отчеством и именем идет поле «idPosition»:

INSERT INTO tbPeoples (vcSurname, idPosition, vcName, vcFamil) VALUES('ПЕТРОВИЧ', 12, 'СЕРГЕЙ', 'СМИРНОВ')

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

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

Листинг 2.5. Создание новой таблицы, копии tbPeoples

CREATE TABLE tbPeoples2 ( -- Описание полей idPeoples2 int, vcFamil varchar(50), vcName varchar(50), vcSurname varchar(50), idPosition int, dDateBirthDay datetime, -- Описание ключей CONSTRAINT PK_idPeoples2 PRIMARY KEY (idPeoples2), CONSTRAINT FK_idPosition2 FOREIGN KEY (idPosition) REFERENCES tbPosition (idPosition), -- Описание ограничений CONSTRAINT check_dDateBirthDay2 CHECK (dDateBirthDay
Так как имена ограничений в таблице должны быть уникальными внутри базы данных, к каждому имени ограничения добавлена цифра 2. В принципе, имя таблицы тоже содержит цифру два (tbPeoples2), т.е. все соответствует тому, чему мы договорились.
Теперь посмотрим, как заполнить новую таблицу записями работников из таблицы tbPooples, и при этом взять только те записи, у которых поле "idPosition" содержит значение более или равное 10. Все достаточно просто:
INSERT INTO tbPeoples2 SELECT * FROM tbPeoples WHERE idPosition>=10
В первой строке пишем оператор INSERT INTO и имя таблицы tbPeoples2, в которую необходимо вставить данные. Затем идет просто запрос SELECT, в котором мы получаем нужные данные из таблицы tbPeoples. Все строки, полученные с помощью этого запроса, будут добавлены в таблицу tbPeoples2.
Все так просто только потому, что обе таблицы содержат одинаковое количество полей, и типы полей совпадают. А если поля будут разными? Давайте создадим третью таблицу tbPeoples3, в которой будут только поля для хранения фамилии, имени и даты рождения.
CREATE TABLE tbPeoples3 ( -- Описание полей vcFamil varchar(50), vcName varchar(50), dDateBirthDay datetime, )
Теперь посмотрим, как можно заполнить эту таблицу значениями:
INSERT INTO tbPeoples3 SELECT vcFamil, vcName, dDateBirthDay FROM tbPeoples WHERE idPosition>=10
Обратите внимание, что мы выбираем с помощью запроса SELECT только три поля и именно в таком порядке, в котором они описаны в таблице tbPeoples3. Названия полей в таблицы, и выбираемые поля могут не совпадать, но типы данных обязаны совпадать полностью, иначе выполнение запроса завершиться ошибкой. Здесь сервер не может производить автоматического преобразования, поэтому, или типы должны совпадать, или вы должны их явно преобразовывать. О явном преобразовании мы поговорим в разделе 2.21.
С помощью оператора INSERT INTO вы можете копировать данные между таблицами или даже переносить их с сервера на сервер с помощью распределенных запросов, о которых мы поговорим в разделе 5.1.
Запрос SELECT в INSERT INTO может быть любой сложности, и тут ограничений на используемые операторы нет. Другое дело, что не всегда сортировка может иметь смысл. Например, если вставлять отсортированные данные в таблицу tbPeoples, то таким образом можно повлиять только на идентификатор добавляемых строк, потому что тут автоматически увеличиваемое поле, которое генерируется последовательно. Но если бы первичный ключ был GUID полем, то сортировка потеряла бы смысл, потому что GUID поле генерируется случайным образом.
Работа с данными через представления

Выборка информации из представлений и ее модификация осуществляются посредством тех же самых инструкций языка Transact-SQL, что и для выборки и модификации информации из базовых таблиц. Эти инструкции, имеющие отношения к представлениям, рассматриваются в последующих подразделах.
Выборка информации из представления
Для всех практических целей представление это то же самое, что и любая базовая таблица базы данных. Поэтому выборку информации из представления можно рассматривать, как преобразование инструкций запроса по представлению в эквивалентные операции по базовым таблицам, на основе которых создано это представление. Такой запрос на выборку данных из представления показан в примере ниже:
USE SampleDb; GO CREATE VIEW view_d2 AS SELECT Id, LastName FROM Employee WHERE DepartamentNumber ='d2'; GO -- Вернет 'Фролов' SELECT LastName FROM view_d2 WHERE LastName LIKE 'Ф%';
Инструкция SELECT в примере выше трансформируется в следующую эквивалентную форму с использованием таблицы из представления view_d2:
SELECT LastName FROM Employee WHERE LastName LIKE 'Ф%' AND DepartamentNumber ='d2';
Далее мы рассмотрим использование представлений с тремя другими инструкциями языка DML: INSERT, UPDATE и DELETE. Модифицирование данных посредством этих инструкций подобно выборке данных. Единственное отличие состоит в том, что для представления, используемого для вставки, модифицирования и удаления данных из таблицы, на основе которой оно создано, существуют некоторые ограничения.
Вставка данных с помощью представления
Инструкцию INSERT можно применять с представлением, как если бы оно было обычной базовой таблицей. Вставляемые в представление строки в действительности вставляются в таблицу в основе представления.
В примере ниже создается представление view_dept, которое содержит первые два столбца таблицы Department. Последующая инструкция INSERT вставляет две строки в таблицу, связанную с представлением, используя значения d4 и 'Разработка'. Столбцу Location, который не вошел в представление view_dept, присваивается значение NULL:
USE SampleDb; GO CREATE VIEW view_dept AS SELECT Number, DepartmentName FROM Department; GO INSERT INTO view_dept VALUES('d4', 'Разработка');
При использовании представления обычно возможно вставить строку, которая не удовлетворяет условиям в предложении WHERE запроса представления. Чтобы ограничить вставку только строками, которые удовлетворяют условиям запроса, применяется предложение WITH CHECK OPTION. При использовании этого предложения компонент Database Engine проверяет каждую вставляемую строку на удовлетворение условий предложения WHERE. Если это предложение отсутствует, такая проверка не выполняется, вследствие чего каждая вставляемая в представление строка также вставляется в таблицу в его основе. Это может вызвать путаницу, когда строка вставляется в представление, но впоследствии не возвращается из этого представления инструкцией SELECT, т.к. для нее принудительно выполняются условия предложения WHERE. Предложение WITH CHECK OPTION также применяется и с инструкцией UPDATE.
В примерах ниже показана разница между применением и неприменением предложения WITH CHECK OPTION соответственно:
USE SampleDb; GO CREATE VIEW view_2007_check AS SELECT EmpId, ProjectNumber, EnterDate FROM Works_on WHERE EnterDate BETWEEN '01.01.2007' AND '12.31.2007' WITH CHECK OPTION; GO INSERT INTO view_2007_check VALUES (9031, 'p2', '1.15.2008'); SELECT * FROM view_2007_check;
В этом примере система проверяет, соответствует ли вставляемое в столбец EnterDate значение True (истина) при вычислении условия в предложении WHERE инструкции SELECT. Если вставляемое значение не удовлетворяет этим условиям, строка не вставляется.
USE SampleDb; GO CREATE VIEW view_2007_nocheck AS SELECT EmpId, ProjectNumber, EnterDate FROM Works_on WHERE EnterDate BETWEEN '01.01.2007' AND '12.31.2007'; GO INSERT INTO view_2007_nocheck VALUES (9031, 'p2', '1.15.2008'); SELECT * FROM view_2007_nocheck;
В результате выполнения этого запроса будут отображены следующие строки:

Поскольку во втором примере предложение WITH CHECK OPTION не применяется, инструкция будет выполнена, и строка вставляется в основную таблицу Works_on. Обратите внимание на тот факт, что вставленная строка не будет возвращена инструкцией SELECT, поскольку ее нельзя извлечь посредством представления view_2007_nocheck.
Вставку строк в таблицу, на которой основано представление, нельзя выполнить, если это представление содержит одну из следующих возможностей:

предложение FROM в определении представления содержит более чем одну таблицу, и список столбцов содержит столбцы более чем из одной таблицы;
столбец в представлении создается из агрегатной функции;
инструкция SELECT в представлении содержит предложение GROUP BY или параметр DISTINCT;
столбец в представлении создается из константы или выражения.

В примере ниже показано представление, которое нельзя использовать для вставки строк в таблицу, на которой основано это представление:
USE SampleDb; GO CREATE VIEW view_Sum (SumBudget) AS SELECT SUM(Budget) FROM Project; GO SELECT * FROM view_Sum; 
Запрос в примере создает представление view_Sum, которое содержит агрегатную функцию sum() в инструкции SELECT. Поскольку представление в этом примере возвращает результат объединения нескольких строк (а не одну строку таблицы Project), то нет смысла пытаться вставить одну строку в базовую таблицу, используя это представление.
Обновление данных с помощью представления
Инструкцию UPDATE можно применять с представлением, как будто бы это была базовая таблица. При модифицировании строк представления также модифицируется содержимое таблицы в его основе. Запрос в примере создает представление, посредством которого затем модифицируется таблица Works_on:
USE SampleDb; GO CREATE VIEW view_p1 AS SELECT EmpId, Job FROM Works_on WHERE ProjectNumber = 'p1'; GO UPDATE view_p1 SET Job = NULL WHERE Job = 'Менеджер';
Операцию обновления представления view_p1 в примере выше можно рассматривать эквивалентной выполнению следующей инструкции UPDATE:
UPDATE Works_on SET Job = NULL WHERE Job = 'Менеджер' AND ProjectNumber = 'p1';
Логическое значение предложения WITH CHECK OPTION для инструкции UPDATE имеет такое же значение, как и для инструкции INSERT. Использование предложения WITH CHECK OPTION в инструкции UPDATE показано в примере ниже:
USE SampleDb; GO CREATE VIEW view_100000 AS SELECT Number, Budget FROM Project WHERE Budget > 100000 WITH CHECK OPTION; GO UPDATE view_100000 SET Budget = 93000 WHERE Number = 'p3';
Здесь компонент Database Engine проверяет, будет ли измененное значение столбца Budget давать значение True в условии предложения WHERE инструкции SELECT. Попытка изменения значения завершается неудачей, поскольку условие не удовлетворяется, т.е. вставляемое значение 93000 не больше, чем значение 100000.
В примере ниже показано представление, которое нельзя использовать для изменения значений в таблице, на которой основано представление:
USE SampleDb; GO CREATE VIEW view_Pound (projectNumber, budgetPounds) AS SELECT Number, Budget * 0.65 FROM Project WHERE Budget > 100000; GO SELECT * FROM view_Pound;
Результат выполнения этого запроса:

Представление view_Pound нельзя использовать с инструкцией UPDATE (или с инструкцией INSERT), поскольку значения столбца budgetPounds являются результатом вычисления арифметического выражения, а не первоначальными значениями столбца таблицы, на которой основано это представление.
Удаление данных с помощью представления
С помощью представления можно удалить строки из таблицы, на которой оно основано, как это показано в примере ниже:
USE SampleDb; GO CREATE VIEW view_project_p1 AS SELECT EmpId, Job FROM Works_on WHERE ProjectNumber = 'p1'; GO DELETE FROM view_project_p1 WHERE Job = 'Консультант';
Запрос в примере создает представление, посредством которого затем удаляются строки из таблицы Works_on. В отличие от инструкций INSERT и UPDATE, инструкция DELETE допускает значения, получаемые из констант или выражений, в столбце представления, используемого для удаления строк из таблицы, на которой оно основано.
В примере ниже показано представление, посредством которого можно удалять строки, но не вставлять строки или изменять значения столбцов:
USE SampleDb; GO CREATE VIEW view_budget (budgetReduction) AS SELECT Budget * 0.9 FROM Project; GO DELETE FROM view_budget;
Инструкция DELETE в примере выше удаляет все строки таблицы Project, на которой основано представление view_budget.
Инструкция INSERT INTO в Transact-SQL – несколько способов добавления данных в таблицу
Всем привет! В данной статье речь пойдет о том, как можно добавлять данные в таблицу в Microsoft SQL Server, если Вы уже хоть немного знакомы с языком T-SQL, то наверно поняли, что сейчас мы будем разговаривать об инструкции INSERT, а также о том, как ее можно использовать для добавления данных в таблицу.
Начнем по традиции с небольшой теории.
Инструкция INSERT в T-SQL
INSERT – это инструкция языка T-SQL, которая предназначена для добавления данных в таблицу, т.е. создания новых записей. Данную инструкцию можно использовать как для добавления одной строки в таблицу, так и для массовой вставки данных. Для выполнения инструкции INSERT требуется разрешение на вставку данных (INSERT) в целевую таблицу.
Существует несколько способов использования инструкции INSERT в части данных, которые необходимо вставить:

Перечисление конкретных значений для вставки;
Указание набора данных в виде запроса SELECT;
Указание набора данных в виде вызова процедуры, которая возвращает табличные данные.


Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.

Упрощённый синтаксис
INSERT [INTO] [таблица] (список столбцов, …) VALUES (список значений, …) Или SELECT запрос на выборку Или EXECUTE процедура 

INSERT INTO – это команда добавления данных в таблицу;
Таблица – это имя целевой таблицы, в которую необходимо вставить новые записи;
Список столбцов – это перечень имен столбцов таблицы, в которую будут вставлены данные, разделенные запятыми;
VALUES – это конструктор табличных значений, с помощью которого мы указываем значения, которые будем вставлять в таблицу;
Список значений – это значения, которые будут вставлены, разделенные запятыми. Они перечисляются в том порядке, в котором указаны столбцы в списке столбцов;
SELECT – это запрос на выборку данных для вставки в таблицу. Результирующий набор данных, который вернет запрос, должен соответствовать списку столбцов;
EXECUTE – это вызов процедуры на получение данных для вставки в таблицу. Результирующий набор данных, который вернет хранимая процедура, должен соответствовать списку столбцов.

Вот примерно так и выглядит упрощённый синтаксис инструкции INSERT INTO, в большинстве случаев именно так Вы и будете добавлять новые записи в таблицы.
Список столбцов, в которые Вы будете вставлять данные, можно и не писать, в таком случае их порядок будет определен на основе фактического порядка столбцов в таблице. При этом необходимо помнить этот порядок, когда Вы будете указывать значения для вставки или писать запрос на выборку. Лично я Вам рекомендую все-таки указывать список столбцов, в которые Вы планируете добавлять данные.
Также следует помнить и то, что в списке столбцов и в списке значений, соответственно, должны присутствовать так называемые обязательные столбцы, это те, которые не могут содержать значение NULL. Если их не указать, и при этом у столбца отсутствует значение по умолчанию, будет ошибка.
Еще хотелось бы отметить, что тип данных значений, которые Вы будете вставлять, должен соответствовать типу данных столбца, в который будет вставлено это значение, ну или, хотя бы, поддерживал неявное преобразование. Но я Вам советую контролировать тип данных (формат) значений, как в списке значений, так и в запросе SELECT.
Хватит теории, переходим к практике.
Исходные данные
Для того чтобы добавлять данные в таблицу, нам нужна соответственно сама таблица, давайте ее создадим, и уже в нее будем пробовать добавлять записи.
Примечание! Все примеры будут выполнены в Microsoft SQL Server 2016 Express.
CREATE TABLE TestTable( [Id] [INT] IDENTITY(1,1) NOT NULL, [ProductName] [VARCHAR](100) NOT NULL, [Price] [Money] NOT NULL )
Наша тестовая таблица, будет содержать перечень товаров с ценой.
Также в примерах мы будем использовать процедуру, которая возвращает табличное значение, для добавления данных в таблицу, поэтому давайте ее тоже создадим.
CREATE PROCEDURE TestProcedure AS BEGIN SELECT ProductName, Price FROM TestTable END
Для примера она у нас будет возвращать данные из только что созданной таблицы TestTable.
Примечание!
Как Вы понимаете, чтение данного материала подразумевает наличные определенных знаний по языку T-SQL, поэтому если Вам что-то непонятно, рекомендую ознакомиться со следующими материалами:

Справочник по Transact-SQL;
Основы программирования на T-SQL;
SQL код – самоучитель по SQL для начинающих программистов;
Видеокурсы по T-SQL.

Пример 1 – Добавляем новую запись в таблицу с использованием конструктора табличных значений
Сначала давайте попробуем добавить одну запись и сразу посмотрим на результат, т.е. напишем запрос на выборку.
INSERT INTO TestTable(ProductName, Price) VALUES ('Компьютер', 100) GO SELECT * FROM TestTable
 
Вы видите, что мы после названия таблицы перечислили через запятую имена столбцов, в которые мы будем добавлять данные, затем мы указали ключевое слово VALUES и в скобочках также, в том же порядке, через запятую написали значения, которые мы хотим вставить.
После инструкции INSERT я написал инструкцию SELECT и разделил их командой GO.
А теперь давайте представим, что нам нужно добавить несколько строк. Мы для этого напишем следующий запрос.
INSERT INTO TestTable(ProductName, Price) VALUES ('Компьютер', 100), ('Клавиатура', 20), ('Монитор', 50) GO SELECT * FROM TestTable
 
В данном случае мы добавили три записи, т.е. три строки. После VALUES значения каждой новой строки указаны в скобочках, разделили мы их запятыми.
Пример 2 – Добавляем новые строки в таблицу с использованием запроса SELECT
Очень часто возникает необходимость добавлять много данных в таблицу, например, на основе запроса на выборку, т.е. SELECT. Для этого вместо VALUES нам всего лишь нужно указать запрос.
INSERT INTO TestTable(ProductName, Price) SELECT ProductName, Price FROM TestTable WHERE Id > 2 GO SELECT * FROM TestTable
 
В данном примере мы написали запрос SELECT, который возвращает данные из таблицы TestTable, но не все, а только те, у которых идентификатор больше 2. А результат вставили все в ту же таблицу TestTable.
В качестве примера того, как можно добавлять записи в таблицу без указания списка столбцов, давайте напишем еще один запрос на вставку данных, который сделает равно то же самое что и запрос выше, только в нем не будет перечисления столбцов для вставки.
INSERT INTO TestTable SELECT ProductName, Price FROM TestTable WHERE Id > 2 GO SELECT * FROM TestTable
 
В данном случае мы уверены в том, что в таблице TestTable первый столбец это ProductName, а второй Price, поэтому мы можем позволить себе написать именно так. Но, снова повторюсь, на практике лучше указывать список столбцов.
Если Вы заметили, я во всех примерах не указывал столбец Id, а он у нас есть, ошибки не возникло, так как данный столбец со свойством IDENTITY, он автоматически генерирует идентификаторы, поэтому в такой столбец вставить данные просто не получится.
Пример 3 – Добавляем новые записи в таблицу с использованием хранимой процедуры
Сейчас давайте вставим в таблицу данные, которые нам вернёт хранимая процедура. Смысл здесь такой же, вместо VALUES и вместо запроса мы указываем вызов процедуры. Но как Вы понимаете, порядок и количество столбцов, возвращаемых процедурой, должен строго совпадать со списком столбцов для вставки (даже если список столбцов не указан).
INSERT INTO TestTable(ProductName, Price) EXEC TestProcedure GO SELECT * FROM TestTable
 
Надеюсь, данный материал помог Вам разобраться с инструкцией INSERT INTO, а у меня все, пока!
Как добавить записи в таблицу sql server
Упражнение 1 - Создание и изменение таблиц
В этом упражнении вы будете выполнять задачи по созданию и изменению таблиц. Вы создадите схемы, предоставите и удалите разрешения, и создадите вычисляемые поля. Чтобы достичь понимания этих тем, пожалуйста обратитесь к материалу курса или воспользуйтесь ссылкой http :// technet . microsoft . com / en - us / library / ms 162575. aspx [ MSDN ].
Вычисляемые и постоянные поля
Вычисляемое поле вычисляется из выражения, что может использовать другое поле или поля в этой же таблице. Имена функций, переменных, констант, невычисляемых полей или любая их комбинация может быть использована вместе с операторами что бы создать вычисляемое поле.
Читать:
Как wix сайт добавить на яндекс
Вычисляемые поля являются виртуальными, и по-умолчанию физически не хранятся в таблице. Их значения вычисляются каждый раз когда на них ссылаются в запросе. Ядро базы данных использует ключевое слово PERSISTED в инструкциях CREATE TABLE или ALTER TABLE что бы физически сохранить вычисляемое поле в таблицу.
В этом примере, мы создадим таблицу которая хранит информацию о сотрудниках, и мы создадим вычисляемое поле Date of Retirement для каждого сотрудника, например: DOBirth + 60 years - 1 day .
Создание вычисляемого поля в инструкции CREATE TABLE
Шаг 1
Откройте Пуск, Все Программы, Microsoft SQL Server 2008 R 2 и запустите SQL Server Management Studio ( SSMS ).
 
Когда SSMS запустится, войдите в Database Engine используя настройки по-умолчанию и откройте новое окно запросов нажав на кнопку New Query слева.
 
Создайте новую таблицу в базе данных AdventureWorks используя следующие команды:
 CREATE TABLE [dbo].[ComputedColumnTest] 
Нажмите кнопку Execute (сверху страницы на панели инструментов со знаком восклицания) или нажмите F 5 что бы выполнить команды.
 
Создайте такое же вычисляемое поле используя SSMS
Шаг 1
В SQL Server Management Studio откройте Object explorer слева ( может быть уже открыт ). Раскройте Databases и AdventureWorks , прав ый клик на объекте Tables в базе данных AdventureWorks , и затем выберите New Table что бы создать новую таблицу.
 
Шаг 2
Создайте такую структуру базы данных:
 Название поля - EmpNumber Тип данных- Int 
 Название поля - DOBirth Тип данных - Datetime 
Поле DORetirement будет нашим вычисляемым полем, раскройте Column Properties внизу страницы, и откройте Computed Column Specification под секцией Table Designer :
 
В спецификации напишите:
 (   dateadd   (   year   ,(60),[   DOBirth   ])-(1))  
 
Так же как и ранее, вы можете создать постоянное поле выбрав Persisted : Yes в постоянном поле:
 
Закройте и сохраните таблицу как ComputedColumnTest 2 нажав на кнопку Закрыть( X ) сверху справа дизайнера таблиц.
 
Обратите внимание, что ваша таблица будет сверху в папке с таблицами пока вы не нажмете кнопку обновить в инспекторе объектов и выполнится сортировка по имени.
 
Вставка значений в вычисляемое поле
Шаг 1
Вставьте значения в ранее созданную таблицу используя инструкцию INSERT INTO как показано ниже в коде. Выполните код в новом окне запроса New Query в Microsoft SQL Server Management Studio :
 INSERT INTO ComputedColumnTest (EmpNumber, DOBirth) 
 
Нажмите кнопку Execute чтобы выполнить SQL код.
 
 Примечание: Поскольку поле DORetirement является вычисляемым, в него не нужно вставлять значения.
Шаг 2
Вы можете увидеть вычисленные значении для поля DORetirement используя предложение SELECT :
Когда напечатаете SQL инструкции, опят нажмите кнопку Execute . 
 
Схемы баз данных
Схема базы данных – это способ логически объединить объекты, такие как таблицы, представления и хранимые процедуры в контейнер. Вы можете назначить разрешения для логина пользователя только для одной схемы, так что пользователь будет иметь доступ к объектам к которым у него есть доступ.
Схемы могут быть созданы и изменены в базе данных и пользователям или ролям может быть разрешен доступ к смехе. Схемой может владеть пользователь или роль и допускается передача владельца схемы.
Создание схемы базы данных используя SSMS
Шаг 1
Откройте SQL Server Management Studio и войдите на сервер баз данных( Database Engine ).
Выберите базу данных AdventureWorks и разверните папку Security из списка. 
 Нажмите правой кнопкой мыши на Schemas и выберите New Schema . 
 
Шаг 2
На вкладке General заполните информацию для новой схемы. В этом примере мы назовем схему APerson и назначим владельцем схемы роль db _ securityadmin .
Напишите имя в поле Schema name , потом нажмите кнопку Search рядом с полем Schema Owner .
 
В поле поиска, напишите : db _ securityadmin и нажмите кнопку Check Names . Это должно поместить объект в квадратные скобки. Когда сделаете нажмите OK .
 
Выберите страницу Permissions на левой панели, потом нажмите кнопку Search , нажмите кнопу Browse и добавьте к схеме роль Public .
 
Нажмите OK , и еще раз OK чтобы вернуться. Теперь вы сможете выбрать необходимые разрешения; в этом примере вы будете устанавливать такие GRANT разрешения:
 
Нажмите OK чтобы закрыть окно.
 
Добавление таблицы к новой схеме
Теперь, когда у нас есть новая схема мы можем добавить в нее объекты, такие как таблицы, представления, и хранимые процедуры. В этом примере, мы будем перемещать существующую таблицу в схеме dbo в только что созданную схему APerson .
Шаг 1
Используя инспектор объектов( object explorer ) в SQL Server Management Studio , разверните Databases > AdventureWorks и нажмите правой кнопкой мыши на таблице ComputedColumnTest (таблица что ранее создана) и выберите Design :
 
В окне Design нажмите F 4 чтобы показать окно Properties . 
 
В окне Properties , измените схему на созданную вами схему:
 
Обратите внимание, вы получите подсказку о том, что изменение схемы сбросит текущие разрешения на объект, нажмите Yes .
 
Закройте окно Design нажав правой кнопкой мыши на вкладке и выбрав Close , сохраните изменения.
 
Создание схемы базы данных используя SQL инструкции
Шаг 1
В SQL Server Management Studio откройте новое окно запросов ( New Query ).
В окне запросов, создайте новую схему используя команду CREATE SCHEMA со следующим синтаксисом.
 CREATE SCHEMA APerson2 AUTHORIZATION db_securityadmin 
 
 Примечание: Если ранее вы создали схему используя SSMS GUI и пытаетесь использовать те же данные схемы, то вам нужно изменить данные или удалить ранее созданную схему.
Шаг 2
Что бы создать таблицу в новой схеме, используйте предложение CREATE TABLE :
 CREATE TABLE APerson2.Employee 
 FirstName NVARCHAR (40), 
 LastName NVARCHAR (40)) 
 
Убедитесь что вы создали таблицу, используя предложение SELECT следующим образом:
 Select * FROM APerson2.Employee 
 
Создание таблицы с таким же именем в другой схеме
Шаг 1
Выполните следующий код с окне запросов:
 CREATE TABLE dbo.Employee 
 FirstName NVARCHAR (40), 
 LastName NVARCHAR (40)) 
 
Шаг 2
Вставьте новые записи в таблицу Employee , что находится в схемах dbo и APerson 2
 INSERT INTO dbo.Employee (EmpNumber, FirstName,LastName ) 
 INSERT INTO APerson2.Employee (EmpNumber, FirstName,LastName ) 
 
Step 3
Напишите запрос к таблице, что расположены в двух разных схемах
 Select * From dbo.Employee 
 Select * From APerson2.Employee 
 
Изменение схемы существующей таблицы
Шаг 1
Мы можем изменить схему существующей таблицы используя опцию TRANSFER предложения ALTER SCHEMA . В этом примере вы измените схему таблицы ComputedColumnTest 2 с dbo на только что созданную схему APerson 2 . Откройте окно запросов и напишите следующий код :
 ALTER SCHEMA APerson2 TRANSFER dbo.ComputedColumnTest2 
 
Вы можете увидеть изменения(обновите панель Object Explorer нажав на кнопку обновить).
Откройте таблицу APerson 2. ComputedColumnTest 2 в дизайнере( Design ), нажмите F 4 что бы увидеть свойства таблицы, обратите внимание на схему:
 
Управление разрешениями
Вы можете использовать предложения GRANT , DENY , and REVOKE что бы давать или забирать разрешения у пользователя или роли.
Предложение GRANT используется что бы давать разрешения пользователю или роли. Используя предложение GRANT возможно назначать разрешение как на объекты, так и на использование предложений. Вы можете использовать предложение GRANT с инструкцией WITH GRANT OPTION , что бы позволить пользователю или роли в будущем получать разрешения на предоставление/отмену доступа к учетным записям пользователей.
Используйте предложение GRANT что бы предоставить разрешение на SELECT
Шаг 1
Откройте новое окно запросов ( New Query ) в SQL Server Management Studio . В окне запросов , напишите следующий запрос :
 GRANT SELECT ON APerson.ComputedColumnTest TO Public 
 
Предложение DENY используется что бы запретить разрешения для защищенной учетной записи в текущей базе данных. Предложение DENY предотвращает защищенную учетную запись от наследования разрешений от группы или роли.
Вы можете использовать предложение DENY чтобы запретить разрешение как на объекты, так и на использование предложений.
Шаг 2
В новом окне запроса, напишите следующий код:
 DENY SELECT ON APerson.ComputedColumnTest TO Public 
 
 Примечание : Если ваш логин является членом роли ddl _ admin , тогда у вас есть права на создание объектов в стандартной схеме dbo . Но если вам было явно запрещено использование предложения SELECT в схеме dbo или любой другой схеме, тогда вы не сможете запрашивать объекты в этой схеме, хотя вы являетесь владельцем объекта.
Шаг 1
В окне запросов, напишите следующий запрос:
 CREATE LOGIN practicelogin WITH PASSWORD=’practice123’, CHECK_POLICY=OFF 
 CREATE USER practiceuser FROM LOGIN practicelogin 
 EXEC sp_addrolemember N'db_ddladmin', N'practiceuser' 
 DENY SELECT ON SCHEMA ::[dbo] TO [practiceuser] 
 
Логин и пользователь успешно создались, так же мы сделали пользователя членом роли ddl _ admin .
Шаг 2
В окне запросов, напишите следующий код:
 EXECUTE AS USER='practiceuser' 
 CREATE TABLE dbo.Practice(Id INT) 
 SELECT * FROM dbo.Practice 
 
Как вы можете видеть, хотя пользователь смог создать таблицу, так как он является членом роли ddl _ admin , попытка запросить созданную таблицу завершилась отказом.
Предложение REVOKE используется что бы отменять ранее созданные GRANT или DENY разрешения пользователя в текущей базе данных. Вы можете использовать предложение REVOKE что бы отменять разрешение как на объекты, так и на использование предложений.
В следующем примере, предложение REVOKE отменяет разрешение на SELECT у роли Public на таблицу APerson . ComputedColumnTest :
Шаг 3
В новом окне запросов, напишите следующий запрос:
 REVOKE SELECT ON APerson.ComputedColumnTest TO Public 
 
 Примечание : Вы можете указать инструкцию GRANT OPTION FOR вместе с предложением REVOKE что бы отменить WITH GRANT OPTION разрешения.
Передача авторизации схемы от одной роли к другой(Изменение владельца схемы)
Шаг 1
Мы можем передать владение схемой от одной роли или пользователя к другой роли или пользователю. Владельцем схемы APerson 2 является роль db _ securityadmin . Мы будем передавать владение схемой роли db _ owner .
Откройте новое окно запросов в SQL Server Management Studio . В новом окне запросов, напишите следующий запрос:
 ALTER AUTHORIZATION ON SCHEMA APerson2::[db_securityadmin] TO [db_owner] 
 
Итог
В этом упражнении, вы научились создавать вычисляемые поля в таблице. Также вы научились создавать схемы и добавлять таблицы в существующие схемы, и передавать владение схемой от одной роли к другой.
Также попробуйте
· Создать таблицу с вычисляемым полем, и проверить разницу если поле создать как постоянное.
· Создать новую схему и предоставить разрешения существующему пользователю в этой схеме.
Упражнение 2 – Создание и изменение представлений
В этом упражнении вы будете выполнять задачи по созданию, изменению, и удалению представлений. Чтобы достичь понимания этих тем, пожалуйста обратитесь к материалу курса или воспользуйтесь ссылкой http :// technet . microsoft . com / en - us / library / ms 162235. aspx [ MSDN ].
Создание представлений
Вы можете создать виртуальную таблицу содержание которой (поля и записи) определяется запросом. Представление может использоваться для следующих целей:
· Для фокусировки, упрощения и настройки просмотра данных для каждого пользователя
· Как механизм безопасности, предоставляя пользователям доступ к данным через представления, без предоставления пользователям разрешения для прямого доступа к базовым таблицам.
· Для обеспечения обратной совместимости интерфейса, для эмуляции таблицы схема которой была изменена.
Создание простого представления используя предложение CREATE VIEW
В этом примере вы создадите представление в базе данных AdventureWorks , что показывает имя, фамилию, и дату приема на работу сотрудников их таблиц Employee и Contact .
Шаг 1
Присоединитесь к Database Engine и откройте новое окно запросов.
В новом окне запросов, напишите следующий запрос на создание простого представления в базе данных AdventureWorks :
 CREATE VIEW hiredate_view 
 SELECT p.FirstName, p.Lastname, e.HireDate 
 FROM HumanResources.Employee e 
 JOIN Person.Contact AS p 
 ON e.ContactID = p.ContactID 
 
Запустите запрос на выполнение.
Шаг 2
Что бы проверить результат, используйте SELECT :
 SELECT * FROM hiredate_view 
 
Создание представления с шифрованием
Свойство WITH ENCRYPTION шифрует данные в sys . syscomments что хранит текст создания представления. Использование свойства WITH ENCRYPTION предотвращает представление от публикации в рамках репликации SQL Server.
В этом примере, вы создадите представление, что бы показать детали отклоненных заказов.
Шаг 1
В SQL Server Management Studio откройте новое окно запросов. В новом окне запросов, напишите следующий запрос что бы создать новое представление в базе данных AdventureWorks с шифрованием:
 CREATE VIEW Purchasing.PurchaseOrderReject 
 SELECT PurchaseOrderID, ReceivedQty, RejectedQty, RejectedQty / ReceivedQty AS RejectRatio, DueDate 
 WHERE RejectedQty / ReceivedQty> 0 
 
Запустите запрос на выполнение.
Шаг 2
Попытайтесь просмотреть SQL код представления используя sp _ helptext 
 
Создание представления с использованием SCHEMABINDING
SCHEMABINDING связывает представление со схемой таблицы или таблиц лежащих в основе. SCHEMABINDING указывает на то, что базовая таблица или таблицы не могут быть изменены таким образом, что бы повлиять на определение представления.
Используя SCHEMABINDING, предложение SELECT должно включать имена из двух частей (schema.object) таблиц, представлений или пользовательских функций, на которые ссылается. Все упомянутые объекты должны быть в той же базе данных.
В этом примере вы создадите представление hiredate _ view , созданное в предыдущем примере с опцией SCHEMABINDING .
Шаг 1
В новом окне запрос, напишите следующие SQL команды:
 CREATE VIEW hiredate_view_sb 
 SELECT p.FirstName, p.LastName, e.HireDate 
 FROM HumanResources.Employee e 
 JOIN Person.Contact AS p 
 ON e.ContactID = p.ContactID 
 
Запустите запрос на выполнение.
Шаг 2
Что бы увидеть работу опции SCHEMABINDING , попытайтесь удалить поле FirstName таблицы Person . Contact . Запрос вернет ошибку как на скриншоте:
 ALTER TABLE Person.Contact 
 DROP COLUMN FirstName 
 
Создание представления с опцией WITH CHECK OPTION
Опция WITH CHECK OPTION проверяет все предложения на изменения данных выполненных по отношению к представлению, соответствовать критерию установленному в предложении SELECT . Когда запись изменена при помощи представления, то опция WITH CHECK OPTION гарантирует, что информация остается видимой из представления после подтверждения изменения.
В этом примере мы создадим представление SeattleOnly , что ссылается на пять таблиц и позволяет изменять данные только сотрудников что проживают в Сиэтле.
Шаг 1
Откройте новое окно запросов в SQL Server Management Studio .
В новом окне запросов, напишите следующий запрос чтобы создать представление с опцией WITH CHECK OPTION в базе данных AdventureWorks :
 CREATE VIEW dbo.SeattleOnly 
 SELECT p.LastName, p.FirstName, a.City, sp.StateProvinceCode 
 FROM Person.Contact p 
 INNER JOIN HumanResources.Employee e 
 ON p.ContactID = e.ContactID 
 INNER JOIN HumanResources.EmployeeAddress ea 
 ON e.EmployeeID = ea.EmployeeID 
 INNER JOIN Person.Address a 
 ON ea.AddressID = a.AddressID 
 INNER JOIN Person.StateProvince sp 
 ON a.StateProvinceID = sp.StateProvinceID 
 WHERE a.City = 'Seattle' 
 WITH CHECK OPTION; 
 
Шаг 2
Проверьте результаты используя предложение SELECT .
 SELECT * from SeattleOnly 
 
Шаг 3
Что бы увидеть как работает опция WITH CHECK OPTION , попробуйте обновить данные представления для города отличного от Сиэтла. Запрос вернет ошибку, как показано ниже.
 WHERE FirstName = 'Jack' 
 
Изменение представления используя предложение ALTER VIEW
Вы можете изменять представление без его удаления. Это так же гарантирует, что разрешение на представление не теряется. Вы можете изменить представление не затрагивая его зависимые объекты, такие как триггера и хранимые процедуры.
Что бы изменить представление, используйте предложение ALTER VIEW .
В этом примере вы будете изменять представление hiredate _ view , чтобы оно включало только сотрудников, которые нанимались до 2000 года.
Шаг 1
В новом окне запроса напишите следующий запрос на изменение hiredate _ view :
 ALTER VIEW hiredate_view 
 SELECT p.FirstName, p.LastName, e.HireDate 
 FROM HumanResources.Employee e 
 JOIN Person.Contact AS p 
 ON e.ContactID = p.ContactID 
 
Шаг 2
Используйте предложение SELECT для проверки результатов:
 SELECT * FROM hiredate_view 
 
Обратите внимание, что тут нет даты найма после 2000 года.
Управление разрешениями
Вы можете использовать предложения GRANT , DENY , и REVOKE что бы предоставить или забрать разрешения у пользователя или роли на представление.
Использование GRANT
В этом примере вы предоставите разрешение SELECT для представления hiredate _ view роли public .
Шаг 1
Откройте новое окно запроса в SQL Server Management Studio . В новом окне запроса напишите следующий запрос, что бы предоставить разрешение SELECT роли public на hiredate _ view :
 GRANT SELECT ON hiredate_view TO Public 
 
Шаг 2
Что бы запретить разрешение SELECT для представления hiredate _ view , просто измените GRANT на DENY :
 DENY SELECT ON hiredate_view TO Public 
 
Шаг 3
Аналогично, что бы отменить разрешения, используйте команду REVOKE :
 REVOKE SELECT ON hiredate_view TO Public 
Пример работы с разрешениями для представлений
Пользователь может не иметь разрешений на SELECT в схеме, но вы все равно хотите предоставить ему доступ к данным таблицы, что принадлежит этой особой схеме, для которой доступ пользователю был запрещен. Мы будем использовать пользователя « practiceuser » и представление « hiredate _ view », что были созданы ранее и чтобы продемонстрировать, каким образом это может быть достигнуто.
Шаг 1
В новом окне запроса, напишите следующий запрос, в случае если вы не создали пользователя practiceuser , тогда пропустите этот шаг:
 CREATE LOGIN practicelogin WITH PASSWORD='practice123', CHECK_POLICY=OFF 
 CREATE USER practiceuser FROM LOGIN practicelogin 
 EXEC sp_addrolememberN'db_ddladmin', N'practiceuser' 
 
 Примечание: Вы получите результат как на скриншоте выше, если не создали пользователя ранее. Если пользователь и логин были уже созданы вы получите следующий результат. Вы пожжете перейти к следующему шагу.
 
Шаг 2
В новом окне запроса, напишите следующий запрос чтобы явно запретить доступ к схемам HumanResources и Person , т.к. на эти две схемы ссылались в представлении hiredate _ view . Так же , предоставьте разрешение на SELECT для схемы dbo:
 GRANT SELECT ON SCHEMA ::[dbo] TO [practiceuser] 
 DENY SELECT ON SCHEMA ::[HumanResources] TO practiceuser 
 DENY SELECT ON SCHEMA ::[Person] TO practiceuser 
 
Шаг 3
В новом окне запроса, напишите следующий запрос, что бы проверить работает ли доступ как предполагалось:
 EXECUTE AS USER='practiceuser' 
 SELECT * FROM hiredate_view 
 SELECT * FROM HumanResources.Employee 
 SELECT * FROM Person.Contact 
 
Таким образом, мы видим, что хотя пользователю был запрещен прямой доступ к объектам определенной схемы, объекты в схеме были доступны пользователю когда ему был дан доступ к представлению, что использовало лежащие в основе таблицы схемы.
Итог
В этом примере, вы узнали как создавать представления с разными опциями, такими как SCHEMABINDING и WITH CHECK OPTION . Так же, вы узнали как изменить представление и управлять разрешениями на представление.
Также попробуйте
· Создание представления для множества таблиц и выполните инструкцию INSERT для представления
· Назначение пользователю Drop разрешения на представление
Упражнение 3 – Создание и Изменение индексов
В этом упражнении вы будете выполнять задачи необходимые для создания и изменения индексов. Чтобы достичь понимания индексов, обратитесь к вашему материалу курса или воспользуйтесь ссылкой http :// technet . microsoft . com / en - us / library / ms 188388. aspx [ MSDN ].
Создание индекса
Индексы увеличивают процесс запроса путем быстрого доступа у записям таблиц данных, аналогично к оглавлению книги, что помогает вам быстро отыскать информацию без этой книги. Если вы создаете индекс на первичном ключе и затем ищете запись с информацией основанной на одном из значений первичного ключа, то SQL Server сначала найдет это значение в индексе и потом использует индекс, что бы быстро обнаружить всю запись информации.
Кластеризованные индексы
Кластеризованные индексы хранят фактические записи информации на промежуточном уровне или на конечном уровне индекса. Важной характеристикой кластеризованного индекса является то, что значения индекса отсортированы в порядке возрастания или убывания.
Некластеризованные индексы
В отличии от кластеризованного индекса, промежуточные уровни или конечные уровни некластеризованного индекса содержат только индексные значения из индексированных полей и записей указателей, что указывают на фактические записи данных, вместо того что бы содержать данные самим.
В этом примере, мы создадим кластеризованный и некластеризованный индекс на таблице клиентов.
Шаг 1
В SQL Server Management Studio откройте новое окно запроса ( New Query ). В новом окне запроса, напишите следующий запрос, что бы создать таблицу Customers в базе данных AdventureWorks :
 CREATE TABLE [dbo].[Customers] 
 ([CustomerId][nchar] (5) NOT NULL, 
 [SocialSecurityNumber][nchar](10) NOT NULL UNIQUE, 
 [ContactName] [NVARCHAR] (30) NOT NULL, 
 [City] [NVARCHAR] (15) NULL) 
 
Шаг 2
Что бы кластеризованный индекс на поле CustomerId , напишите в окне запроса:
 CREATE CLUSTERED INDEX idx_Customers_Custid 
 ON Customers ([CustomerId]) 
 
Шаг 3
Что бы создать некластеризованный индекс на поле CompanyName , напишите в окне запросов следующие команды:
 CREATE NONCLUSTERED INDEX idxnc_Customers_CompanyName 
 ON Customers ([CompanyName]) 
 
Уникальный индекс
Уникальный индекс – это по умолчанию некластеризованный индекс в котором запрещены повторения значений индекса. В этом примере вы создадите уникальный индекс на таблице Customers , которую вы ранее создали.
Шаг 1
Откройте новое окно запроса. В новом окне запроса, напишите следующий запрос, что бы создать уникальный индекс на поле SocialSecurityNumber в таблице Customers :
 CREATE UNIQUE INDEX idx_uniq_ssn 
 ON Customers (SocialSecurityNumber) 
 
Отфильтрованный индекс
Отфильтрованный индекс был представлен в SQL Server 2008, - это оптимизированная разновидность некластеризованного индекса. Индекс применяется только к подмножеству информации в таблице и полезен для покрытия запросов, что возвращают малый процент информации из строго определенного подмножества информации и таблице.
В этом примере, мы создадим отфильтрованный индекс на поле ContactName для всех клиентов в городе Seattle .
Шаг 1
Откройте новое окно запроса. В новом окне запроса, напишите следующий запрос, что бы создать отфильтрованный индекс на поле в таблице Customers :
 CREATE NONCLUSTERED INDEX idx_filtered_ctnm_city 
 ON Customers (ContactName) 
 WHERE City = 'Seattle' 
 
Создание индексов с включенными столбцами
Вы можете расширить функциональность некластеризованных индексов путем добавления неключевых полей на конечном уровне некластеризованного индекса. Добавляя неключевые поля, вы можете создавать некластеризованные индексы, что покрывают больше полей. Это потому что, неключевые поля имеют следующие перимущества:
· Они могут иметь тип данных, что не поддерживается в качестве ключа индекса.
· Они не учитываются ядром базы данных при подсчете количества ключей индексов или размера ключа индекса.
В этом примере вы создадите индекс на поле ContactName и включите в него поле City :
Шаг 1
Откройте новое окно запроса. В новом окне запроса, напишите следующий запрос что бы создать некластеризованный индекс с включенным полем для таблицы Customers :
 CREATE INDEX idxnc_Customers_ContactName 
 ON Customers ([ContactName]) 
 
Коэффициент заполнения
Коэффициент заполнения указывает % заполнения страниц конечного уровня индекса. Когда индекс создается или перестаивается, то страницы конечного уровня записываются на уровне который содержит заполненные страницы согласно значения коэффициента заполнения и остаток страниц остается пустым для будущего использования. Значение 0 или 100 означает, что процент количества пустых страниц данных не указан.
В этом примере, мы создадим некластеризованный индекс на поле CustomerId с коэффициентом заполнения равным 50.
Шаг 1
Откройте новое окно запроса. В новом окне запроса напишите следующий запрос что бы создать индекс с коэффициентом заполнения:
 CREATE NONCLUSTERED INDEX idx_Customers_CustomerId 
 ON Customers ([CustomerId]) WITH 
 
Создание статистики
SQL Server собирает статистическую информацию об индексах и полях данных, что хранятся в базе данных. Статистика используется оптимизатором SQL Server -а что бы выбрать наиболее эффективный план извлечения или обновления информации.
Разница между инструкциями CREATE INDEX и CREATE STATISTICS
Инструкция CREATE INDEX в первую очередь генерирует объявленный индекс и также создает один набор статистики для комбинации полей составляющих ключи индекса(но не для включенных полей). Инструкция CREATE STATISTICS только генерирует статистику для данного поля или комбинации полей.
В этом примере вы создадите статистику( CREATE STATISTICS ) для таблицы Customers и используете опцию Sample , что бы указать приблизительный процент количества записей таблицы или индексированного представления для оптимизатора запросов, что используется когда статистика создается.
Шаг 1
Откройте новое окно запросов в SQL Server Management Studio ( SSMS ). В новом окне запроса напишите следующий запрос, что бы создать статистику для таблицы Customers :
 CREATE STATISTICS CustomerId 
 ON Customers (CustomerID, SocialSecurityNumber) 
 WITH SAMPLE 50 PERCENT 
 
Индексированные представления
Создание уникального кластеризированного индекса для представления делает из него индексированное представление. Уникальный кластеризированный индекс хранится в SQL Server и обновляется как любой другой кластеризированный индекс, обеспечивая SQL Server другим взглядом на оптимизацию путем использования индексированного представления. Что бы использовать уникальный кластеризированный индекс представление должно быть связанно со схемой. В отличие от обычного представления, индексированные представления хранятся отдельно.
Шаг 1
Откройте новое окно запросов. В новом окне запросов, напишите следующий код, что бы создать представление с опцией SCHEMABINDING : 
 CREATE VIEW ProductSold_View 
 FROM Production.Product p 
 INNER JOIN Sales.SalesOrderDetail s 
 
Шаг 2
Создайте уникальный кластеризованный индекс для представления на поле SalesOrderDetailID :
 CREATE UNIQUE CLUSTERED INDEX idx_ProductSold 
 
Шаг 3
Используйте предложение SELECT с планом выполнения, что бы увидеть использование индекса. Нажмите на кнопку Include Actual Execution Plan на панели инструментов или используйте меню Query > Include Actual Execution Plan . Обратите внимание на количество показанных операторов в плане выполнения после выполнения запроса. В этом случае их двое, и запрос использует сканирование кластеризованного индекса, потому что мы не отфильтровали запрос при помощи других операторов.
 SELECT * FROM ProductSold_View 
 
Шаг 4
Что бы понять преимущество использования индексированных представлений вместо обычных представлений, сначала удалим индексированное представление, что мы создали и затем создадим обычное представление.
В новом окне запроса, напишите следующий запрос:
 DROP VIEW ProductSold_View 
Шаг 5
Теперь создайте обычное представление тем же запросом, что мы использовали для индексированного представления. В новом окне запроса напишите следующий запрос :
 CREATE VIEW ProductSold_View_Without_Index 
 FROM Production.Product p 
 INNER JOIN Sales.SalesOrderDetail s 
 
Шаг 6
В новом окне запроса, напишите следующий запрос. Нажмите кнопку Include Actual Execution Plan на панели инструментов или используйте меню Query > Include Actual Execution Plan . В этом случае план выполнения показывает 4 оператора для запроса, что почти вдвое больше если сравнивать с планом выполнения для запроса использующего индексированные представления, что означает большую общую стоимость запроса чем в индексированном представлении:
 SELECT * FROM ProductSold_View_Without_Index 
 
Примечание: Индексированные представления довольно полезны если лежащие в основе таблицы не изменяются. Любая из инструкций Insert , Update или Delete для таблиц лежащих в основе индексированного представления, заставит SQL Server выполнить их и для индексированного представления. Понимание этого имеет первостепенную важность.
Итог
В этом упражнении вы узнали как создавать индексы для таблиц и представлений.
Также попробуйте
· Создать первичный ключ на таблице, если кластеризованный индекс уже существует.
· Создать статистику на данных выборки для представления.
О нас
Предоставялем решения в области ИТ и разработки программного обеспечения.
Специализированно занимаемся администрированием, разработкой, поддержкой на платформе SQL Server c 2008 года.
Контакты

Россия, Москва, Озерковская набережная д. 50, строение 1
+7 495 409-3421
193231, Россия, Санкт-Петербург, ул. Коллонтай 21-1-22
+7 812 715-3421
info@spbdev.biz

Мы в соцсетях
Свяжитесь с нами
Предоставим Вам бесплатную консультацию специалиста
© SPBDEV, Все права защищены